Worksheet Names Stored In Cells For Excel Formula References

VBA - Worksheet Names From Cells In Excel Formulas

Got any Excel/VBA Questions? Free Excel Help

IMPORTANT. To understand this page read this page first. Variable Worksheet Names in Excel Formulas

DYNAMIC WORKSHEET NAME LIST Download Workbook Example

The first thing we should do is create a dynamic named range for our list of Worksheet names. Go to Insert>Name>Define and use the name "Worksheet_Names" and have it refer to:

=OFFSET($B$2,0,0,COUNTA($B$2:$B$20),1)

Next we need a macro that we can Call which will add the 2 needed formulae.

One that adds the CELL formula automatically when we add a new Worksheet.

One that adds the MID and FIND formula that parses out the Worksheet name from the CELL formula.

The code for this goes in a standard public module and is as shown below

Sub AddCellFormulas(strShName As String)

    With Sheet1.Cells(Rows.Count, 1).End(xlUp)

        .Offset(1, 0).FormulaR1C1 = _

            "=CELL(""filename""," & strShName & "!R1C1)"

        .Offset(1, 1).FormulaR1C1 = _

            "=""'""&MID(RC[-1],FIND(""]"",RC[-1])+1,256)&""'!"""

    End With

End Sub

"Sheet1" is the worksheet code name

Note the Procedure takes an argument as a String and this String is then used in the CELL formula that is added.

Next we need to access the Private Module of the Workbook Object (ThisWorkbook). If in the VBE. Simply double click ThisWorkbook. If in Excel proper, right click on Excel icon, top left next to File and choose View Code. It is in here we use the code below.

Private Sub Workbook_NewSheet(ByVal Sh As Object)

    If Sh.Type = xlWorksheet Then

        Call AddCellFormulas(Sh.Name)

    End If

End Sub

In the Event code (which fires anytime a Sheet is added) it first checks to ensure the Sheet Type is a standard Excel Worksheet. If it is, the code uses Call to run the Procedure "AddCellFormulas" and parses the all important worksheet name to it.

Next we need some more Event code that will clear out any redundant formula (added by "AddCellFormulas") when/if a Worksheet is deleted. If a worksheet is deleted the CELL formula that used its name within will result in #REF! as the Worksheet no longer exists. This will result in the MID and FIND formula also returning #REF! as it references a #REF! cell. With this in mind we can use the Calculate Event of the Worksheet Object housing these formulae to clear any error cells. Right click on the Worksheet name (one that AddCellFormulas adds the formulas to) and choose View Code. In here paste the code below.

Private Sub Worksheet_Calculate()

    On Error Resume Next

    Application.EnableEvents = False

        With Me.UsedRange

            .SpecialCells(xlCellTypeFormulas, xlErrors).Clear

            .Sort Key1:=Range("B2"), Order1:=xlAscending, _

                Header:=xlYes, OrderCustom:=1, _

                MatchCase:=False, Orientation:=xlTopToBottom

        End With

    Application.EnableEvents = True

    On Error GoTo 0

End Sub

Now, each time we add a new Worksheet, we will have the new name in the Validation list. Anytime we delete a Worksheet its name will be cleared and the Worksheet sorted to prevent blanks.

Gallery

stars (0 Reviews)

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
OzGrid is in no way associated with Microsoft.

Website designed by Astrava Solutions Ltd.