How to Get Sheet Name in Excel VBA?

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
How to Get Sheet Name in Excel VBA?

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
How to Get Sheet Name in Excel VBA?
Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Hey, wait!Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.