Dynamic Worksheet Names

[Guest]

I inserted the following code for each worksheet tab to dynamically change
the sheet name to match the value in the designated target cell in that
worksheet:

Private Sub Worksheet_Activate()
ActiveSheet.Name = Range("C7").Value
End Sub


It works OK with one quirk that I don't know how to fix.

When I change the value in the target worksheet cell (C7), the worksheet tab
name does not change until I click on another worksheet tab, then click back
on the worksheet tab I just changed.

How do I make the tab name change as soon as the target cell is updated ?

Thanks !!!

 

[Guest]

It's because your using the activate event which only runs when the sheet is
(well) activated, Try this instead

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.Name = Range("C7").Value

End Sub


Mike

 

[Guest]

Mike .....

Worked like a charm ! I'm a novice at code writing to say the least. Thank
you so much not only for the help, but for such a prompt reply.

Regards ...

 

[Guest]

Try this

Private Sub Worksheet_Activate()
Dim WS As Worksheet
Dim aWS As Worksheet
Dim myWS As Worksheet

Set aWS = ActiveSheet
Set myWS = Nothing

For Each WS In ActiveWorkbook.Worksheets
If WS.Name = aWS.Range("C7") Then
Set myWS = WS
End If
Next WS

If myWS Is Nothing Then
aWS.Name = aWS.Range("C7").Value
Else
MsgBox ("There is already a worksheet with the name " &
aWS.Range("C7").Value)
End If

End Sub

HTH,
Barb Reinhardt