How to determine if a sheet exists

[katie1071]

Hi,

Need help with VB. I need to check if a Sheet exists before switching to it and processing.

For example, a user could delete a worksheet called out in a module. If so, the module will fail.

In the code, each sheet is called out by Sheet#.Select. I just want to verify it exists before moving forward. The user could also rename the sheet so I need to call it out by the Sheet#.

Thanks,
Katie

[geekgirlau]

First step is to name your sheet in the VBE window. When you click on a sheet in the Project Explorer, if you display the Properties Window you can assign a name to the sheet that has nothing to do with the caption displayed on the sheet tab. That way it doesn't matter whether the user renames it. I'd recommend that you don't refer to the sheet by number as the user may also change the order of the tabs.

Each time you refer to the sheet, you can do so by name. So instead of

Sheets("Time").Select

you can use

shTime.Select

Assuming that you have assigned the name "shTime" to your sheet, you can use the following to test whether the sheet exists:


On Error Resume Next
shTime.Select

If Err.Number = 0 Then
' perform processing here
End If

[Jacob Hilderbrand]

Try this:


Option Explicit

Sub Macro1()

Dim ws As Worksheet

On Error Resume Next
Set ws = Sheets("Sheet1")
On Error GoTo 0

If ws Is Nothing Then
MsgBox "Sheet does not exist."
Else
MsgBox "Sheet exists."
End If
Set ws = Nothing

End Sub


Also if you let the users rename the sheets, then you should use the Code Name for the sheet. You can set this in the Properties window on the VBE when the Sheet code module is selected.

[geekgirlau]

And it's geekgirlau by a nose hair :giggles:

[katie1071]

Thank you both...renaming the sheet in the project explorer window makes sense. It didn't register to me that by referring to the sheets by number equate to the order of the sheets! thanks!

[ravvio31]

Try this:


Option Explicit

Sub Macro1()

Dim ws As Worksheet

On Error Resume Next
Set ws = Sheets("Sheet1")
On Error GoTo 0

If ws Is Nothing Then
MsgBox "Sheet does not exist."
Else
MsgBox "Sheet exists."
End If
Set ws = Nothing

End Sub


Also if you let the users rename the sheets, then you should use the Code Name for the sheet. You can set this in the Properties window on the VBE when the Sheet code module is selected.

I am working on a project whereby i want to copy excel data from multiple workbooks or multiple sheets which may or may not exists everytime. How can i use the above functionality for the same. Please assist.

Regards,

Ravi Dubey

[snb]

To test whether the sheet "new" exists and if not create it:


Sub M_snb()
if [not(isref(new!A1))] then sheets.add(,sheets(sheets.count)).name="new"
End Sub

[mikerickson]

Function SheetExists(sheetName as String, optional CreateIfGone as Boolean) As Boolean
On Error Resume Next
SheetExists = (ThisWorkBook.Sheets(sheetName).Name = sheetName)
On Error Goto 0
If Not (CreateIfGone Imp SheetExists) Then
With ThisWorkbook.Sheets
.Sheets.Add(After:= .Sheet(.Sheets.Count)).Name = sheetName
End With
SheetExists = True
End IF
End Function

[SamT]

MIke, What is "Imp"?

[mikerickson]

IMP is a Boolean operator :

True Imp True = True
True Imp False = False
False Imp True = True
False Imp False = True

[SamT]

Thanks

[mikerickson]

In my college Logic class, IMP was the only atomic operator, i.e. all other logical operators were defined from IMP

Not(A) is [A Imp False]

[A Or B] is [Not(B) Imp A]

[A And B] is [(A Or B) Imp Not(A Imp Not(B))]

[A Eqv B] is [(A Imp B) And (B Imp A)]

[A Xor B] is [Not (A Eqv B)]

[SamT]

We dint have them atoms in my logic class ca 1970. :D