Home

Auto naming tab based on cell value

Highlighted
Trisa58
Occasional Contributor

I have the following VBA formula to automatically change the tab name based on a cell value:

 

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "C5" Then Me.Name = Target.Value
End Sub


I have this formula in C5 "=CONCATENATE(TEXT($K$1,"mmm")," ",TEXT($K$1,"dd")," - ",TEXT($K$2,"mmm")," ",TEXT($K$2,"dd"))"  - K1 being a beginning date (eg. 10/4/2019) and K2 being an ending date (eg, 10/10/2019) so resulting value is "Oct 4 - Oct 10" which is what I want the tab named. This part does automatically update.  However, the macro to name the tab only works if I a) manually enter the information or b) press F2 and then enter (effectively editing the cell). 

Is there a way to change the macro to automatically update the tab, or will I need to press F2 each time?

1 Reply

@Trisa58

 

Hi,

 

Try this code instead:

Private Sub Worksheet_Calculate()
Me.Name = Range("C5").Value
End Sub

 

Regards

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies