I have the following code that's not working as expected:
If Sheets("a") <> "" Then MsgBox ("sheet a exists")
How can I tell if sheet a exists in the workbook?
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.Sign up to join this community
I'd make a separate function for it:
Function SheetExists(SheetName As String) On Error GoTo no: WorksheetName = Worksheets(SheetName).Name SheetExists = True Exit Function no: SheetExists = False End Function
Then you can easily call it where needed, even in a formula if you wanted:
Sub ABC() If SheetExists("Test") Then MsgBox "Yay!" Else MsgBox "Boo!" End If End Sub
In the template that I made for AutoFilter, I used a function and procedure to check whether a worksheet is available. In the codes below, it is checked whether the DATA sheet is in the workbook :
Sub Sht_Cntrl() If Not Sheet_Exists_Cntrl("DATA") Then ThisWorkbook.Sheets.Add( _ After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = "DATA" End If End Sub Function Sheet_Exists_Cntrl(SheetName As String) As Boolean Dim sht As Excel.Worksheet On Error GoTo eHandle Set sht = ThisWorkbook.Worksheets(SheetName) Sheet_Exists_Cntrl = True Exit Function eHandle: Sheet_Exists_Cntrl = False End Function
When the Sht_Cntrl procedure is called (
Call Sht_Cntrl) it is created if DATA sheet does not exist.
You can view the codes by downloading the sample template from this page : Vba autofilter with listbox