Changing Tab Name Based on Cell Value - Round 2

patski107

New Member
Joined
Oct 6, 2016
Messages
8
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
 
Last edited:

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Oct 6, 2016
Messages
8
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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.
Code:
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

New Member
Joined
Oct 6, 2016
Messages
8
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.
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,481
Messages
5,601,918
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top