Posts by Iridium

From http://www.excel-center.com/mailing/032198h.htm

'Place the code below into the standard module
Sub Tally()
Dim WB As Workbook
    For Each WB In Workbooks
        If WB.Name <> ThisWorkbook.Name Then
            WB.Close savechanges:=False
        End If
    Next WB
End Sub

HTH

Quote

Originally posted by jjst34
awesome.. nice find! Love it,, does exactly what i need. thanks!

Good link isn't it? You might consider marking the thread as solved if that's your answer

Glad to be of help!

Check out Daniel Klann's excellent code here:

http://www.danielklann.com/exc…ext_in_a_vba_inputbox.htm

HTH

Try this on for size!

http://www.erlandsendata.no/en…vbaprintselectprintertray

HTH

Check out Ivan's page also:

http://www.xcelfiles.com/VBA_Quick13.html

Tweak the code to suit

HTH

Not sure I understand completely but check out 'OnKey' in the VBE help:

ENTER (numeric keypad) {ENTER}
ENTER ~ (tilde)

using Application.OnKey etc.

HTH

Also posted here:

http://www.tek-tips.com/viewth…ID=762546&SPID=707&page=1

Hmmm...

From the xl help file on 'specifications':

Length of cell contents (text) 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.

Column width 255 characters

And the help file on 'Correct a ##### error'
Occurs when a column is not wide enough, or a negative date or time is used.

Review the possible causes and solutions.

Possible causes and solutions
Column is not wide enough to display the content

Increase the width of the column Select the column, point to Column on the Format menu, click Width, and then enter a number.

Shrink the contents to fit the column Select the column, then on the Format menu, click Cells, click the Alignment tab, and then select the Shrink to fit check box.

Apply a different number format In some cases, you can change the cell's number format to make the number fit within the existing cell width. For example, decrease the number of decimal places after the decimal point.

Dates and times are negative numbers

If you are using the 1900 date system, dates and times in Microsoft Excel must be positive values.

When you subtract dates and times, make sure you build the formula correctly.

If the formula is correct, although the result is a negative value, you can display the value by formatting the cell with a format that is not a date or time format. Click Cells on the Format menu, click the Number tab, and then select a format that is not a date or time format.

Any help?

Quote from "big02"

Should i name the ranges before clearing them?

Dunno - have you tried it?

I'm no VBA expert but one way to speed up some code is to use

Application.ScreenUpdating  =False
'your code here
Application.ScreenUpdating = True

HTH

Quote

Originally posted by X-man
My VBA code password protects all sheets every time I open this Spreadsheet.
But when I try to refresh the data, I get an error "Cannot Edit Pivottable on protected sheet"
How could I, using VBA,
1)unlock the sheets
2)refresh the Povottables
3)lock the sheets again

Thanks in advance

Display More

Try something on these lines:

Sub Test () 


ActiveSheet.Unprotect "Password" 
    'your pivot table refresh code - record this with the macro recorder on an unprotected sheet
ActiveSheet.Protect "Password" 
End Sub

HTH

Also try

<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"&gt;</SCRIPT&gt;<CENTER&gt;<TABLE cellSpacing=0 cellPadding=0 align=center&gt;<TBODY&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=5&gt;<TABLE width="100%" align=center border=0&gt;<TBODY&gt;<TR&gt;<TD align=left&gt;<FONT color=white&gt;Microsoft Excel - Book1</FONT&gt;</TD&gt;<TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right&gt;___Running: xl2002 XP : OS = Windows XP </FONT&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=5&gt;<TABLE width="100%" align=center border=0 VALIGN="MIDDLE"&gt;<TBODY&gt;<TR&gt;<TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption"&gt;<U&gt;F</U&gt;ile <U&gt;E</U&gt;dit <U&gt;V</U&gt;iew <U&gt;I</U&gt;nsert <U&gt;O</U&gt;ptions <U&gt;T</U&gt;ools <U&gt;D</U&gt;ata <U&gt;W</U&gt;indow <U&gt;H</U&gt;elp <A onclick=show_popup(); href="#javascript:void(0)"&gt;<U&gt;A</U&gt;bout</A&gt;</TD&gt;<TD vAlign=center align=right&gt;<FORM name=formCb663764&gt;<INPUT onclick='window.clipboardData.setData("Text",document.formFb685530.sltNb237554.value);' type=button value="Copy Formula" name=btCb592514&gt;</FORM&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=5&gt;<TABLE border=0&gt;<TBODY&gt;<TR&gt;<FORM name=formFb685530&gt;<TD style="WIDTH: 60px" align=middle bgColor=white&gt;<SELECT onchange="document.formFb685530.txbFb761501.value = document.formFb685530.sltNb237554.value" name=sltNb237554&gt;<OPTION value="=B2-A2+(A2&gt;B2)" selected&gt;C2</OPTION&gt;</SELECT&gt;</TD&gt;<TD align=right width="3%" bgColor=#d4d0c8&gt;<B>=</B&gt;</TD&gt;<TD align=left bgColor=white&gt;<INPUT size=80 value="=B2-A2+(A2&gt;B2)" name=txbFb761501&gt;</TD&gt;</FORM&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<BR&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;A</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;B</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;C</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;D</CENTER&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;1</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"&gt;Start&nbsp;date&nbsp;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"&gt;End&nbsp;date&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"&gt;No.hours</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"&gt;&nbsp;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;2</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"&gt;03/06/2003&nbsp;13:00</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"&gt;13/06/2003&nbsp;09:30</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"&gt;<A onclick="document.formFb685530.sltNb237554.options[0].selected=true; document.formFb685530.txbFb761501.value = document.formFb685530.sltNb237554.value;" href="#javascript:void(0);"&gt;236:30:00</FONT&gt;</A&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid"&gt;&nbsp;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan=5&gt;<TABLE width="100%" align=left VALIGN="TOP"&gt;<TBODY&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left&gt;<U&gt;Sheet1</U&gt;</TD&gt;<TD&gt;&nbsp;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;<BR&gt;<FONT color=#339966 size=1&gt;[HtmlMaker light Ver1.10] </FONT&gt;<FONT color=#339966 size=1&gt;To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT&gt;<BR&gt;<FONT color=red size=1&gt;PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT&gt;</CENTER&gt;

result cell formatted as [hh]:mm:ss and input cells as dd/mm/yyyy hh:mm

HTH

Try something like this in the ThisWorkbook module:

Private Sub Workbook_Open()
Sheets("Sheet1").Select
End Sub

Some stuff on the following page at Jon Peltier's site you might find handy:

http://peltiertech.com/Excel/XL_PPT.html

HTH

Cheers Andy - that's the one!

Using xl to send mail (using a macro) via Outlook - how to avoid the security message? I know it's a security issue but need a way round it please. I think there was a thread a while ago that addressed this but I can't find it - I don't want to use SendKeys btw...

Quote

Originally posted by jjst34
Oh, Also, how can i make it bypass that message that says "all sheets will be deleted blah blah..."?? Thanks!

For this bit use Application.DisplayAlerts=False at the start of your code and remember to set to True at the end. Not sure about your main question though - sorry!

Not sure (not my territory this!) but I think this is relevant

http://j-walk.com/ss/excel/tips/tip29.htm

HTH

Have you tried AutoFilter or an Advanced Filter?

Sorry Derk - obviously I have a goldfish's memory!