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:
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.
|Excel VBA Code For Excel UserForms & Controls|
|TextBox for Numbers Only|
|TextBox for Text Only|
|Using Variables in Excel VBA Macro Code|
|Excel VBA Variables Scope and Lifetime|
|Index to Excel VBA Code|
See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.