Worksheet Names



Tab/Display Name

You can refer to your worksheets in VBA code using there programmatic name.
This "name" property can be viewed from the Properties window in the VBE.

Sheet2.Range(A2").Select 

CodeName

The following code always return ThisWorkbook

Debug.Print ActiveWorkbook.CodeName 

This can only be used with ThisWorkbook

Debug.Print ActiveWorkbook.Worksheets(1).CodeName 


Getting the Name of the Worksheet

One way of obtaining the name of the worksheet to appear in one of its cells is to use the Application.Caller property
This property returns a reference to the object that called (or executed) the procedure
When used in a user-defined worksheet function it returns a reference to the cell containing the formula

Public Function BET_GetWorkSheetName() As String 
   Application.Volatile(True)
   Bet_GetWorksheetName = Application.Caller.Parent.Name
End Function

Getting the Name of the previous Worksheet

This worksheet function will always return the name of the previous worksheet.
The worksheet name could then be used in an INDIRECT function.

Public Function BET_PreviousSheet() As String 
Dim isheetcount As Integer
Dim ssheetname As String

   Application.Volatile(True)
   ssheetname = Application.Caller.Parent.Name
   BET_PreviousSheet = "N/A"
   For isheetcount = 2 To ActiveWorkbook.Worksheets.Count
      If ActiveWorkbook.Worksheets(isheetcount).Name = ssheetname Then
         BET_PreviousSheet = ActiveWorkbook.Worksheets(isheetcount - 1).Name
      End If
   Next isheetcount
End Function


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext