Microsoft Excel - tab name = cell value - Asked By justin olden on 23-Apr-09 01:43 PM

After reading a post from last year on this subject I tried to use the following code in the VBA worksheet to make the tab name the same as the date in a specific cell on the same sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "n3"
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = .Value
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Yet, when I hit alt-q to go back and test, I could not get it to work.

This workbook has 52 sheets, one for each week and needing that date for each week on the tab.  

Clearly I am doing something wrong and thought you all may have an idea of a solutions.

thank you


Make sure the code is in the sheet itself - Boko Moko replied to justin olden on 21-May-09 02:42 PM

Are you shure the code above is in the right place ? 

To capture the change event this code must be in the sheet itself. Right click on the its name and select "view code", insert the sub there.

Also, make sure the WS_RANGE has "$N$3"  ok ?