Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tab Names
Is there a way to have the worksheet tab names reflect the same name that is
in another worksheet and another cell without having to change (18) worksheet tab names. The cell and worksheet would remain the same, however occasional the name in that cell would change (as per new release's, P1, P2, P3, etc) and I would like the tab to automatically update the new name. Any thoughts Thanks Frank |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tab Names
Something like this?
Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "H1:H10" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target On Error Resume Next Worksheets(.Row).Name = .Value On Error GoTo 0 End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Beep Beep" wrote in message ... Is there a way to have the worksheet tab names reflect the same name that is in another worksheet and another cell without having to change (18) worksheet tab names. The cell and worksheet would remain the same, however occasional the name in that cell would change (as per new release's, P1, P2, P3, etc) and I would like the tab to automatically update the new name. Any thoughts Thanks Frank |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tab Names
Bob:
Don't mean to sound dumb, but how do you run this after you put it in the worksheet event code. When I try to run a macro it does not list this. I can see it but when I hit the run button it goes to another macro. This is my first time putting a macro in one of the worksheet event codes. Thanks Frank "Bob Phillips" wrote: Something like this? Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "H1:H10" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target On Error Resume Next Worksheets(.Row).Name = .Value On Error GoTo 0 End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Beep Beep" wrote in message ... Is there a way to have the worksheet tab names reflect the same name that is in another worksheet and another cell without having to change (18) worksheet tab names. The cell and worksheet would remain the same, however occasional the name in that cell would change (as per new release's, P1, P2, P3, etc) and I would like the tab to automatically update the new name. Any thoughts Thanks Frank |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tab Names
Change one of the cells in H1:H10 of the worksheet that you attached the
code to, and note the results. The code is triggered by the change. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Beep Beep" wrote in message ... Bob: Don't mean to sound dumb, but how do you run this after you put it in the worksheet event code. When I try to run a macro it does not list this. I can see it but when I hit the run button it goes to another macro. This is my first time putting a macro in one of the worksheet event codes. Thanks Frank "Bob Phillips" wrote: Something like this? Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "H1:H10" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target On Error Resume Next Worksheets(.Row).Name = .Value On Error GoTo 0 End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Beep Beep" wrote in message ... Is there a way to have the worksheet tab names reflect the same name that is in another worksheet and another cell without having to change (18) worksheet tab names. The cell and worksheet would remain the same, however occasional the name in that cell would change (as per new release's, P1, P2, P3, etc) and I would like the tab to automatically update the new name. Any thoughts Thanks Frank |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting email address names in a range of cells to real names | Excel Worksheet Functions | |||
using the Excel generic worksheet names instead of user-given names in code | Excel Discussion (Misc queries) | |||
Sorting and matching rows of names with Socials with master list and eliminating the extra names | Excel Worksheet Functions | |||
return all worksheet tab names and chart sheet tab names in report - an example | Excel Programming | |||
Change names of files in a folder to match names in Excel Column | Excel Programming |