Dynamic Worksheet Tab
- Thread starter jpwbvi
- Start date
My guess is using macros where once the cell data is updated you have to activate a macro to change the name of the worksheet.
But know that there is a limit to the size (around 30 I don't remember exactly) of the naming area on all worksheets. You can't have a cell with 100 character and have the tab taking it all.
But know that there is a limit to the size (around 30 I don't remember exactly) of the naming area on all worksheets. You can't have a cell with 100 character and have the tab taking it all.
Hi
Try the following code, it must be placed in the code for the sheet you want to change and it assumes that the name of the Sheet is in Cell A1
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Address <> "$A$1" Then
Exit Sub
End If
ActiveSheet.Name = Target.Value
End Sub
Try the following code, it must be placed in the code for the sheet you want to change and it assumes that the name of the Sheet is in Cell A1
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Address <> "$A$1" Then
Exit Sub
End If
ActiveSheet.Name = Target.Value
End Sub
I'd recommend changing the one line in kchiba code from:
ActiveSheet.Name = Target.Value
to:
Me.Name = Target.Value
Why? Me is generally a safer object to use, as it clearly defines what sheet you want to change (the one with code in it). If you select multiple sheets (and let's say they all have this change event), ActiveSheet would refer to the single sheet that's active, and only 1 sheet name would be changed, while the Me would allow the code to affect each sheet name.
Finally, do note that some symbols aren't allowed in sheet names, as well as the name "History", so you might actually want an error avoidance like:
On Error Resume Next
Me.Name = Target.Value
On Error Goto 0
ActiveSheet.Name = Target.Value
to:
Me.Name = Target.Value
Why? Me is generally a safer object to use, as it clearly defines what sheet you want to change (the one with code in it). If you select multiple sheets (and let's say they all have this change event), ActiveSheet would refer to the single sheet that's active, and only 1 sheet name would be changed, while the Me would allow the code to affect each sheet name.
Finally, do note that some symbols aren't allowed in sheet names, as well as the name "History", so you might actually want an error avoidance like:
On Error Resume Next
Me.Name = Target.Value
On Error Goto 0