Naming multiple parameters/worksheets

[Guest]

Hello again,
Thanks Ron for answering my earlier question, i wont have chance to get to
my excel work till later so im just gonna ask this on measly thing before
work. If i want to add more worksheets into this code:

If SheetExists("wksht 3") = True Then

Can it be done like this?

If SheetExists("wksht 3", "wksht 4") = True Then

Or will i need some more code. If i had excel with me id try it out but i
just dont wana be faffing around later is all. If anyone can help then its
much appreciated.
Thanks in advance
Andrewbt

 

[Guest]

Hi,

You can pass the arguments to the funxtion like this:-

If SheetExists("Sheet1") Or SheetExists("Sheet2") = True Then

or if you want to check that both exist:-

If SheetExists("Sheet1") AND SheetExists("Sheet2") = True Then

Mike

 

[Bob Phillips]

It can do it exactly that way if you change your SheetExists code


Function SheetExists(ParamArray sheets())
Dim i As Long
Dim sh As Worksheet
Dim cnt As Long

For i = LBound(sheets) To UBound(sheets)
Set sh = Nothing
On Error Resume Next
Set sh = Worksheets(sheets(i))
On Error GoTo 0
If Not sh Is Nothing Then
cnt = cnt + 1
End If
Next i

'if you want to check if any exist then use
SheetExists = cnt > 0

'if you want to checl all exists then use
SheetExists = cnt = UBound(sheets) - LBound(sheets) + 1

End Function




--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)