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
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies