Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Worksheet Names
I inserted the following code for each worksheet tab to dynamically change
the sheet name to match the value in the designated target cell in that worksheet: Private Sub Worksheet_Activate() ActiveSheet.Name = Range("C7").Value End Sub It works OK with one quirk that I don't know how to fix. When I change the value in the target worksheet cell (C7), the worksheet tab name does not change until I click on another worksheet tab, then click back on the worksheet tab I just changed. How do I make the tab name change as soon as the target cell is updated ? Thanks !!! -- HOOS77 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Worksheet Names
It's because your using the activate event which only runs when the sheet is
(well) activated, Try this instead Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.Name = Range("C7").Value End Sub Mike "HOOS77" wrote: I inserted the following code for each worksheet tab to dynamically change the sheet name to match the value in the designated target cell in that worksheet: Private Sub Worksheet_Activate() ActiveSheet.Name = Range("C7").Value End Sub It works OK with one quirk that I don't know how to fix. When I change the value in the target worksheet cell (C7), the worksheet tab name does not change until I click on another worksheet tab, then click back on the worksheet tab I just changed. How do I make the tab name change as soon as the target cell is updated ? Thanks !!! -- HOOS77 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Worksheet Names
Mike .....
Worked like a charm ! I'm a novice at code writing to say the least. Thank you so much not only for the help, but for such a prompt reply. Regards ... -- HOOS77 "HOOS77" wrote: I inserted the following code for each worksheet tab to dynamically change the sheet name to match the value in the designated target cell in that worksheet: Private Sub Worksheet_Activate() ActiveSheet.Name = Range("C7").Value End Sub It works OK with one quirk that I don't know how to fix. When I change the value in the target worksheet cell (C7), the worksheet tab name does not change until I click on another worksheet tab, then click back on the worksheet tab I just changed. How do I make the tab name change as soon as the target cell is updated ? Thanks !!! -- HOOS77 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Worksheet Names
Try this
Private Sub Worksheet_Activate() Dim WS As Worksheet Dim aWS As Worksheet Dim myWS As Worksheet Set aWS = ActiveSheet Set myWS = Nothing For Each WS In ActiveWorkbook.Worksheets If WS.Name = aWS.Range("C7") Then Set myWS = WS End If Next WS If myWS Is Nothing Then aWS.Name = aWS.Range("C7").Value Else MsgBox ("There is already a worksheet with the name " & aWS.Range("C7").Value) End If End Sub HTH, Barb Reinhardt "HOOS77" wrote: I inserted the following code for each worksheet tab to dynamically change the sheet name to match the value in the designated target cell in that worksheet: Private Sub Worksheet_Activate() ActiveSheet.Name = Range("C7").Value End Sub It works OK with one quirk that I don't know how to fix. When I change the value in the target worksheet cell (C7), the worksheet tab name does not change until I click on another worksheet tab, then click back on the worksheet tab I just changed. How do I make the tab name change as soon as the target cell is updated ? Thanks !!! -- HOOS77 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
dynamic XY chart names | Excel Discussion (Misc queries) | |||
How to make worksheet names dynamic? | Excel Worksheet Functions | |||
Dynamic Worksheet Names | Links and Linking in Excel | |||
formula to set up dynamic range in names | Excel Worksheet Functions | |||
Dynamic Worksheet Names | Excel Worksheet Functions |