Dynamic Worksheet Tab Naming

[mmel]

I would like to have the name tab of a worksheet change to the entry in
a cell.
I have multiple sheets that all reference a master sheet. The master
sheet is the place where the user sets up the information for the other
sheets.
There are cells in the master setup sheet, where the user enters the
names of individual pieces of equipment. Each worksheet contains data
pertaining to one piece of equipment.
I found some VB code on Excel Tips and Tricks that works, but it only
renames the active sheet from a cell within it's own sheet.
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Set Target = Range("A1")
If Target = "" Then Exit Sub
On Error GoTo Badname
ActiveSheet.Name = Left(Target, 31)
Exit Sub
Badname:
MsgBox "Please revise the entry in A1." & Chr(13) _
& "It appears to contain one or more " & Chr(13) _
& "illegal characters." & Chr(13)
Range("A1").Activate
End Sub
I could use this and link a cell in the individual equipment's sheets
to the master sheet to get the name to the equipment sheets, but this
VB code only works on the active sheet. I would want it to update all
the tabs when names are entered in the master sheet, regardless of
whether the equipment sheets are active or not.
This workbook is used as template for many different groups of
equipment that are constantly changing, thus I cannot create a static
template for each group of equipment.
I hope this makes sense.

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:A" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each sh In ActiveWorkbook.Worksheets
i = i + 1
sh.Name = Cells(i, "A").Value
Next sh
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[mmel]

Thanks for the response, Bob.
Questions:
1) Do I have to put this into each worksheet's code module?
2) Is the 'string' the range of cells that the equipment names are
entered into on the master sheet?
Mike

 

[Bob Phillips]

No, just put it with the master sheet. Any time you change a value in the
list of sheet names, it will go and update them. Do leave A1 as the master
sheet name though.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[mmel]

Thanks, Bob, but I still can't seem to get this to work.
I changed this Const WS_RANGE As String = "A:A" '<=== change to suit

to B48:B61, which is the range of cells on the Master Sheet containing
the equipment names.
The individual sheets use A1 as the name cell, but the Master Sheet
does not.
??????