There could be a time when you have to check if a worksheet, which you have create or deleted in a workbook in a VBA macro / code, exists. We can do this easily using a function / macro. There are multiple ways of checking if a worksheet exists.
We will cover the following ways in this article:
1. User Defined Function known as UDF
2. Sub routine through message box
First option: User Defined Function
Following snapshot contains few sheets names & we will check if the names of sheet in column A exist.
To find if a specific sheet exists, we need to follow the below steps to launch VB editor
Option Explicit Function WorksheetExists(ByVal WorksheetName As String) As Boolean Dim Sht As Worksheet For Each Sht In ThisWorkbook.Worksheets If Application.Proper(Sht.Name) = Application.Proper(WorksheetName) Then WorksheetExists = True Exit Function End If Next Sht WorksheetExists = False End Function
This function takes the value for “WorksheetName” from the macro which performs other activities. If you need to change it as per your code, you may.
For Each Sht In ThisWorkbook.Worksheets and Next Sht are the starting and ending parts of the loop respectively.
Then If Application.Proper(Sht.Name) = Application.Proper(WorksheetName) Then
WorksheetExists = True
Checks if the Sheet name is matching the Sheet name passed from the main macro. If it does, WorksheetExists is True, and we can exit the function. Otherwise, WorksheetExists = False is returned back to the main macro. The loop goes from the 1st sheet to the next until all the sheets have been checked.
Second Option: Sub routine through message box
We can have a normal subroutine which is calling a UDF and, if the specified sheet is found, the message box will display, ‘sheet exist’; if not found, then msgbox pops up, ‘sheet not found’.
To check, we will copy the following code in the standard module:
Function WorksheetExists2(WorksheetName As String, Optional wb As Workbook) As Boolean If wb Is Nothing Then Set wb = ThisWorkbook With wb On Error Resume Next WorksheetExists2 = (.Sheets(WorksheetName).Name = WorksheetName) On Error GoTo 0 End With End Function Sub FindSheet() If WorksheetExists2("Sheet1") Then MsgBox "Sheet1 is in this workbook" Else MsgBox "Oops: Sheet does not exist" End If End Sub
After running the macro “FindSheet”, we will get the following message box if sheet exists:
If Sheet does not exist we will get the following message box:
Similarly, we can have a simple IF loop which checks if the sheet exists and performs certain actions thereafter.
Sub test() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name <> "Main" Then ws.Range("A1").Value = ws.Name Else ws.Range("A1").Value = "MAIN LOGIN PAGE" End If Next ws End Sub
Conclusion: We can identify whether a sheet exists in our workbook or not; we can use UDF or subroutine as per our convenience.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at firstname.lastname@example.org
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.