How do I test for the existance of a worksheet?

[John]

I have the name of a desired worksheet in the variable SheetName.

What form of an If statement do I use to execute some code if this worksheet
exists?

I appreciate your help, -John

 

[Tim Zych]

Create a reusable function...you'll use it again and again..something like:

Function WksExists(wkb As Workbook, WksName As String) As Boolean
Dim wks As Worksheet
WksExists = False
For Each wks In wkb.Worksheets
If wks.Name = WksName Then
WksExists = True
Exit Function
End If
Next
End Function


If WksExists(ThisWorkbook, "Sheet2") Then
'Code
End If

 

[got.sp4m]

Here's an example with the same way of thinking but with a bit simpler
implementation:

Function WsExists(wb As Workbook, strWsName As String) As Boolean
On Error Resume Next
WsExists = Not wb.Worksheets(strWsName) Is Nothing
End Function

best regard
Peder Schmedling

 

[Brettjg]

Hi Tim, that's very cool and useful. I have a question though. Thefollowing
code works as it is, but the commented-out code returns a ByRef argument error

Sub find_worksheet()
mysht = Application.InputBox("ENTER WORKSHEET TO FIND", "SHEET NAME")
Range("G1").Value = mysht
'If WksExists(ThisWorkbook, mysht) Then
' MsgBox "SHEET '" & mysht & "' exists in this workbook"
'Else
' MsgBox "SHEET NOT FOUND: '" & mysht & "'"
'End If

If WksExists(ThisWorkbook, Range("G1").Value) Then
MsgBox "SHEET '" & mysht & "' exists in this workbook"
Else
MsgBox "SHEET NOT FOUND: '" & mysht & "'"
End If
End Sub

I don't quite understand why I need to put the inputbox value into a cell to
get the value back into a format that is correct. There must be a better way
of doing that.........Regards, Brett

 

[Tim Zych]

That's valid too Peter.

One thing which I had in mind doing it the long way was that if "Break On
All Errors" is checked, the shortcut way will halt on all instances where
the worksheet does not exist. If I'm debugging some code I might not want
the macro to halt in a helper function such as this. Practically a non-issue
99.9% of the time but it's something worth considering.

Regards,
--
Tim Zych
http://www.higherdata.com


Here's an example with the same way of thinking but with a bit simpler
implementation:

Function WsExists(wb As Workbook, strWsName As String) As Boolean
On Error Resume Next
WsExists = Not wb.Worksheets(strWsName) Is Nothing
End Function

best regard
Peder Schmedling

 

[Frederik]

Have a look at

http://spreadsheetpage.com/index.php/tip/some_useful_vba_functions/

Success!!

 

[Tim Zych]

With a couple modifications yours works

Sub find_worksheet()
Dim mysht As String
mysht = InputBox("ENTER WORKSHEET TO FIND", "SHEET NAME")
If StrPtr(mysht) = 0 Then
MsgBox "cancelled."
Exit Sub
ElseIf WksExists(ThisWorkbook, mysht) Then
MsgBox "SHEET '" & mysht & "' exists in this workbook"
Else
MsgBox "SHEET NOT FOUND: '" & mysht & "'"
End If
End Sub