Check if worksheet exists.... ?

Pat_The_Bat

Board Regular
Joined
Jul 12, 2018
Messages
80
Trying to put a snip of code at the beginning of an Add-In I've created that will check to make sure the required worksheet exists before it tries to run the code (to avoid error).

Below is not working. Any direction/guidance is appreciated.


HTML:
Dim sht As WorksheetDim wb As Workbook
If wb Is Nothing Then Set wb = ActiveWorkbook

On Error Resume NextSet sht = wb.Sheets(Table)If worksheetExists = sht ThenMsgBox ("The Sheet Table Exists. Click Ok to Format the UW Approval")

GoTo FormatApprovalElse
MsgBox ("This Add-In will only run if there is a worksheet tab titled 'TABLE'")Exit Sub
End If
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,335
Office Version
  1. 365
Platform
  1. Windows
You could use a function for that
Code:
Public Function ShtExists(ShtName As String, Optional Wbk As Workbook) As Boolean
    If Wbk Is Nothing Then Set Wbk = ActiveWorkbook
    On Error Resume Next
    ShtExists = (LCase(Wbk.Sheets(ShtName).Name) = LCase(ShtName))
    On Error GoTo 0
End Function
And called like
Code:
   If Not ShtExists("Sheet1") Then
      MsgBox "sheets doesn't exist"
   End If
or
Code:
   Set Wb = Workbooks("Fluff.xlsm")
   If Not ShtExists("sheet1", Wb) Then
      MsgBox
   End If
 

Watch MrExcel Video

Forum statistics

Threads
1,109,007
Messages
5,526,247
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top