Worksheet Name = Formula Value

[meflorence]

Is there a way, VBA or otherwise, that allows a Sheet Name to reflect a
value entered into any Cell? For instance; Cell A1 contains the word
"Help". Could I then have the Sheet Name [tab 1] read "Help"? I know
a cell value can return a sheet name but I am unsure this relationship
can work in reverse. Any assistance would be greatly appreciated.

…Best

 

[Frank Kabel]

Hi
you have to use VBA to achieve this. Put the following code in your
worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
Application.EnableEvents = False
Me.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

This will change the worksheet name to the value in A1

 

[Ron de Bruin]

Or this one in the Thisworkbook module
It is working for all sheets in the workbook

This example will only work if you change A1

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$A$1" Then
If Target.Value <> "" Then
On Error Resume Next
Sh.Name = Target.Value
On Error GoTo 0
End If
End If
End Sub

If you change A1 in each sheet of the workbook the tab name will change to the cell value


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)

Hi
you have to use VBA to achieve this. Put the following code in your
worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
Application.EnableEvents = False
Me.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

This will change the worksheet name to the value in A1

--
Regards
Frank Kabel
Frankfurt, Germany

Is there a way, VBA or otherwise, that allows a Sheet Name to reflect
a value entered into any Cell? For instance; Cell A1 contains the
word "Help". Could I then have the Sheet Name [tab 1] read "Help"?
I know a cell value can return a sheet name but I am unsure this
relationship can work in reverse. Any assistance would be greatly
appreciated.

.Best

 

[meflorence]

Perfect! Thanks to all