Worksheet Names

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 
   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

   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

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