Worksheet Name = Formula Value

  • Thread starter Thread starter meflorence
  • Start date Start date
M

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
 
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
 
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)




Frank Kabel said:
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
Click to expand...
Click to expand...
 
Back
Top