How do I test for the existance of a worksheet?
 

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

How do I test for the existance of a worksheet?
 

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


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


"John" wrote in message
...
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

How do I test for the existance of a worksheet?
 

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

On Feb 3, 2:46*am, "Tim Zych" <feedback at higherdata dt com wrote:
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

--
Tim Zychhttp://www.higherdata.com

"John" wrote in message

...

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

How do I test for the existance of a worksheet?
 

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" wrote:

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


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


"John" wrote in message
...
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

How do I test for the existance of a worksheet?
 

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


wrote in message
...
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

On Feb 3, 2:46 am, "Tim Zych" <feedback at higherdata dt com wrote:
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

--
Tim Zychhttp://www.higherdata.com

"John" wrote in message

...

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

How do I test for the existance of a worksheet?
 

Have a look at

http://spreadsheetpage.com/index.php...vba_functions/

Success!!

--
met vriendelijke groetjes

"John" schreef in bericht
...
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

How do I test for the existance of a worksheet?
 

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

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


"Brettjg" wrote in message
...
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" wrote:

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


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


"John" wrote in message
...
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