Changing Tab Name Based on Cell Value - Round 2

[patski107]

I have a spreadsheet I use to track my time. Each tab is a pay period and I would like for the tab name to automatically change based on the cell containing the last day of the pay period, in my case F1. RickXL was kind enough to respond to an earlier post with the following code but I can't get it to work. I'm sure it is operator error since what I know about Excel would fit in a thimble.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F1")) Is Nothing Then
On Error GoTo quit
Me.Name = Format(Range("F1").Value, Range("F1").NumberFormat)
On Error GoTo 0
End If
Exit Sub

quit:
MsgBox "Sheet name: " & Range("F1").Text & " is not valid."
End Sub

​

I think I am pasting it in the correct location. I went into VBA (Alt +F11), dbl clicked on Sheet 1 which opened a screen, Sheet1 (Code), and I pasted the code. I hit Run and I get a box that says Macro Name (which is empty) I put in tabname and hit Create. Now I have a screen that says Module1 (Code) Sub tabname() End Sub. What goes in Module1?

Pat

 

[JLGWhiz]

I have a spreadsheet I use to track my time. Each tab is a pay period and I would like for the tab name to automatically change based on the cell containing the last day of the pay period, in my case F1. RickXL was kind enough to respond to an earlier post with the following code but I can't get it to work. I'm sure it is operator error since what I know about Excel would fit in a thimble.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F1")) Is Nothing Then
On Error GoTo quit
Me.Name = Format(Range("F1").Value, Range("F1").NumberFormat)
On Error GoTo 0
End If
Exit Sub

quit:
MsgBox "Sheet name: " & Range("F1").Text & " is not valid."
End Sub

​

I think I am pasting it in the correct location. I went into VBA (Alt +F11), dbl clicked on Sheet 1 which opened a screen, Sheet1 (Code), and I pasted the code. I hit Run and I get a box that says Macro Name (which is empty) I put in tabname and hit Create. Now I have a screen that says Module1 (Code) Sub tabname() End Sub. What goes in Module1?

Pat

To use the code. Once you have installed RickXL's code in the worksheet code module, Close the VB Editor and save your workbook as a macro enabled workbook, if you have not already done so. Make sure any other code you installed as a result of this exercise is cleared out so it will not interfere with RickXL's code. The code is event code and is triggered to run by making a change on the worksheet. The code you have will not execute any noticeable changes unless cell F1 is the cell where the change occurred. Before closing the VB editor, make sure you are out of design mode. The design mode icon is the triangle with pencil and ruler. If it is highlighted, click it to remove the highlight or the event trigger will be in an off state until you exit the design mode.

 

[patski107]

To use the code. Once you have installed RickXL's code in the worksheet code module, Close the VB Editor and save your workbook as a macro enabled workbook, if you have not already done so. Make sure any other code you installed as a result of this exercise is cleared out so it will not interfere with RickXL's code. The code is event code and is triggered to run by making a change on the worksheet. The code you have will not execute any noticeable changes unless cell F1 is the cell where the change occurred. Before closing the VB editor, make sure you are out of design mode. The design mode icon is the triangle with pencil and ruler. If it is highlighted, click it to remove the highlight or the event trigger will be in an off state until you exit the design mode.

Thanks Whiz, but I still can't get it to work. I followed your instructions. I'm doing something wrong. There doesn't seem to be a way to insert an image on the Forum without a URL. I put a couple of screenshots in my dropbox, I hope you can open them.
https://www.dropbox.com/s/vnfxz0bvwil54bp/module.PNG?dl=0
https://www.dropbox.com/s/x8e068aili5vpb7/Timesheet.PNG?dl=0

F1 has a formula that adds 13 days to D1 which inserts the date of the end of the pay period. In order to test what I did I copied the worksheet and changed the date in D1 which caused the date in F1 to change. If I did everything correctly the name of the tab should have changed to the value in F1 - it didn't. Also, if this macro only works if there is a change in the value of the cell then I will have to manually name the first tab since the value of F1 won't change.

Pat

 

[JLGWhiz]

Thanks Whiz, but I still can't get it to work. I followed your instructions. I'm doing something wrong. There doesn't seem to be a way to insert an image on the Forum without a URL. I put a couple of screenshots in my dropbox, I hope you can open them.
https://www.dropbox.com/s/vnfxz0bvwil54bp/module.PNG?dl=0
https://www.dropbox.com/s/x8e068aili5vpb7/Timesheet.PNG?dl=0

F1 has a formula that adds 13 days to D1 which inserts the date of the end of the pay period. In order to test what I did I copied the worksheet and changed the date in D1 which caused the date in F1 to change. If I did everything correctly the name of the tab should have changed to the value in F1 - it didn't. Also, if this macro only works if there is a change in the value of the cell then I will have to manually name the first tab since the value of F1 won't change.

Pat

If Excel sees this as a calculation it will not trigger the Change event. That is probably why you are not seeing the results you want. Try changing the macro title line to:
Private Sub Worksheet_Calculate()
then see if the macro runs when you make the change in D1

 

[JLGWhiz]

You could write the macro another way to do what you want I believe. If Cell E24 is always the end of your pay period then the code below should work.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("D1")) Is Nothing Then
    If Range("F1").Value = Range("C24").Value Then
        On Error GoTo quit:
            Me.Name = Format(Range("F1").Value, "mm-dd-yyyy")
        On Error GoTo 0
    End If
quit:
    If Err.Number > 0 Then
        MsgBox Err.Number & ":  " & Err.Description & vbCrLf & "Cell F1 not properly configured", vbInformation, "Error"
        Err.Clear
    End If
End If
Application.EnableEvents = True
End Sub

 

[patski107]

Thanks for all the assistance. I finally found a code I could make work.


Private Sub Worksheet_Calculate()
If Range("f1").Text <> Me.Name Then Me.Name = Range("f1").Text
End Sub

Now on to the next problem.

Pat

You could write the macro another way to do what you want I believe. If Cell E24 is always the end of your pay period then the code below should work.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("D1")) Is Nothing Then
    If Range("F1").Value = Range("C24").Value Then
        On Error GoTo quit:
            Me.Name = Format(Range("F1").Value, "mm-dd-yyyy")
        On Error GoTo 0
    End If
quit:
    If Err.Number > 0 Then
        MsgBox Err.Number & ":  " & Err.Description & vbCrLf & "Cell F1 not properly configured", vbInformation, "Error"
        Err.Clear
    End If
End If
Application.EnableEvents = True
End Sub

 

[pile-it Mark]

i get "invalid use of Me keyword"?