Auto Update Sheet Tab

K

Karen

I have a date in cell D1 and (of course) it changes every day. Is there a
way of auto updating the sheet tab name when the date changes in cell D1?

Thank you, Karen
 
J

Jason Morin

Right-click on the worksheet tab, select View Code, and paste in the
following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Me.[D1], Target) Is Nothing Then
On Error GoTo InvalidName
Me.Name = Target.Value
Exit Sub
End If
InvalidName:
MsgBox "Invalid sheet name."
End Sub

---
Press ALT+Q to close the VBE.

HTH
Jason
Atlanta, GA
 
K

Karen

Thank you for your help - I pasted the code as you mentioned and the sheet
tab is not changing. I exited the file and reopened it and nothing happened.
Am I doing something wrong?
 
J

Jason Morin

Make sure you select "Enable Macros" upon opening your file. I would also
check Tools > Options > Security tab and click the "Macro Security" button.
Make sure it is not set to "High."

What I gave was some code I posted recently. I should have tested it before
giving it to you. Replace the code I originally posted with this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Me.[D1], Target) Is Nothing Then
On Error GoTo InvalidName
Me.Name = Format(Target.Value, "mm-dd-yy")
Exit Sub
End If
InvalidName:
MsgBox "Invalid sheet name."
End Sub

---
HTH
Jason
Atlanta, GA
 
Top