I asked for help recently to create a macro that takes the text in cell A1 on every worksheet and renames all the tabs to that text. It may not be exactly what you are looking for, but it might get you started. Here's the code:
Dim Msg As String, i As Integer
For i = 1 To Sheets.Count
If Sheets(i).Range("A1").Value = "" Then
Msg = "Sheet " & i & "(" & Sheets(i).Name & ") has no value in A1. Fix sheet, then rerun."
MsgBox Msg, vbExclamation
On Error GoTo ErrSheetName
Sheets(i).Name = Sheets(i).Range("A1").Value
On Error GoTo 0
ErrSheetName: Msg = "Sheet " & i & "(" & Sheets(i).Name & ") could not be renamed. Check if name already used."
MsgBox Msg, vbExclamation
Hey I'm having trouble with this function. I have followed the instructions carefully - and at one time it worked... but I reopened the file and it seems that now I reference the function. It shows up in my fx bar and I hit "tab" to input the correct syntax but it will only display the "#NAME?" error. Help?
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o> Your code for renaming a worksheet tab via referencing a cell works pretty well, but I’m still having trouble getting the tab to rename itself automatically as opposed to me manually having to highlight the cell function in the function bar and then pressing ENTER. Allow me to explain further.
<o> </o> I have a single workbook with two worksheets; worksheet A and worksheet B. What I’m trying to do is have cell B1 in worksheet B rename its tab with the content from cell B1. However, I have cell B1 functioned to populate the text data from cell A1 of worksheet A. In essence, I have =A!A1 as the function inside cell B1 of worksheet B. When I enter in, let’s say XXXXX in cell A1 of worksheet A, it populates cell B1 in worksheet B, but worksheet B’s tab does not automatically rename itself from the functioned result in cell B1. For some reason, the only way I can get the tab to rename “automatically” is go up to the function bar, highlight the function, and then press ENTER.
<o> </o> I was wondering if you may be able to help me out on resolving this. Whether it’s an adjustment to the VBA code you mentioned or maybe a setting within Excel 2003, I would very much appreciate any assistance you can provide. Thank you!
<o> </o> Ashton
If you're having the same problem I was, Damon's new code took care of the problem. Simply add the code below to you VB Editors code window, save your file, and your tab will rename itself automatically if the cell the tab is referencing contains the result of a function or formula. Kudos goes out to Damon!
Private Sub Worksheet_Calculate()
If Range("B1").Text <> Me.Name Then Me.Name = Range("B1").Text