In this post, you’ll learn about with a detailed explanation of how to get sheet name in Microsoft Excel using VBA.
How to Get Sheet Name in Excel VBA?
Sheet names are stored in the Name property of the Sheets or Worksheets object. The Sheet Name is the “tab” name that’s visible at the bottom of Excel.
Get ActiveSheet Name
To display the ActiveSheet name in a message box, use the below code snippet
MsgBox ActiveSheet.Name
Get Sheet Name by index Number
To display the worksheet name in a message box by its index number:
MsgBox Sheets(1).Name
To display the name of the last worksheet in the workbook:
MsgBox Sheets(Sheets.Count).Name
Get Sheet Name by Code Name
In the VBA Editor, there is an option to change the “code name” of a Sheet. The code name is not visible to the Excel user and can only be seen in the VBA Editor:
The VBA code name:
CodeName.Activate
To get the Sheet name in a MsgBox using the VBA Code name:
MsgBox CodeName.Name
Check if Sheet Name Exists
This is used to check whether the sheet name exists already.
Enter the following code in the module and click run
Code:
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
