Linking Tab Name to a Cell

D

Dave H

I have been unsuccessful in my search but I am sure there is an easy way
to change the tab name to the value in cell. How do I do that?

Thanks
Dave
 
O

Otto Moehrbach

Dave
The simple answer is:
ActiveSheet.Name = Range("A1").Value
But this is VBA code so it begs the question: When do you want this name
change to happen?
Your post subject mentions "Linking". Do you want this to happen
automatically whenever cell A1 (in my example) changes content?
If so, then this macro will do that. Note that this macro is a sheet macro
and must be placed in the sheet module of the sheet in question. To access
that module, right-click on the sheet tab. select View Code, and paste this
macro into that module. "X" out of the module to return to your sheet. HTH
Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then _
Me.Name = Range("A1").Value
End Sub
 
G

Gord Dibben

Only through VBA, not via any Excel Function.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
On Error Resume Next
Me.Name = Range("A1").Text
On Error GoTo 0
End If
End Sub

This is event code. Right-click on the sheet tab and "View Code"

Copy/paste the code into that module.

Make a change in A1 and sheet name will change.


Gord Dibben MS Excel MVP
 
Top