Home

Auto naming tab based on cell value

%3CLINGO-SUB%20id%3D%22lingo-sub-894582%22%20slang%3D%22en-US%22%3EAuto%20naming%20tab%20based%20on%20cell%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-894582%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20the%20following%20VBA%20formula%20to%20automatically%20change%20the%20tab%20name%20based%20on%20a%20cell%20value%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EIf%20Target.Address(False%2C%20False)%20%3D%20%22C5%22%20Then%20Me.Name%20%3D%20Target.Value%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EEnd%20Sub%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20have%20this%20formula%20in%20C5%20%3CEM%3E%22%3DCONCATENATE(TEXT(%24K%241%2C%22mmm%22)%2C%22%20%22%2CTEXT(%24K%241%2C%22dd%22)%2C%22%20-%20%22%2CTEXT(%24K%242%2C%22mmm%22)%2C%22%20%22%2CTEXT(%24K%242%2C%22dd%22))%22%26nbsp%3B%20-%20%3C%2FEM%3EK1%20being%20a%20beginning%20date%20(eg.%2010%2F4%2F2019)%20and%20K2%20being%20an%20ending%20date%20(eg%2C%2010%2F10%2F2019)%20so%20resulting%20value%20is%20%22Oct%204%20-%20Oct%2010%22%20which%20is%20what%20I%20want%20the%20tab%20named.%20This%20part%20does%20automatically%20update.%26nbsp%3B%26nbsp%3BHowever%2C%20the%20macro%20to%20name%20the%20tab%20only%20works%20if%20I%20a)%20manually%20enter%20the%20information%20or%20b)%20press%20F2%20and%20then%20enter%20(effectively%20editing%20the%20cell).%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20change%20the%20macro%20to%20automatically%20update%20the%20tab%2C%20or%20will%20I%20need%20to%20press%20F2%20each%20time%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-894582%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-894748%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20naming%20tab%20based%20on%20cell%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-894748%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F328864%22%20target%3D%22_blank%22%3E%40Trisa58%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%20code%20instead%3A%3C%2FP%3E%3CPRE%3EPrivate%20Sub%20Worksheet_Calculate()%3CBR%20%2F%3E%20%20%20Me.Name%20%3D%20Range(%22C5%22).Value%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E
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
Highlighted

@Trisa58

 

Hi,

 

Try this code instead:

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

 

Regards

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 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