Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking worksheet tab 2, 3, 4 ... names to cells in worksheet 1
Situation:
Regional HQ is provided with template workbook for reporting activity of their branches. Worksheet 1 is the region summary of worksheets 2, 3, 4, ... which hold the individual branch details. When setting up Regions change the name of the branch column headings on worksheet 1 from "Branch n" to the location name. It is highly desirable that at the same time as a branch column heading is changed, the name tab of worksheet n+1 is also changed. My Challenges: I have read several posts on this subject (Bob Phillips & Gert - 12/9/2006, Dave Peterson & Big Rick - 1/14/2006) and have a reasonable understanding of the code solution, subject to some questions below. The big challenge is how to drop the code into the worksheet! Can't find any guidance in the Excel Help/VB Reference - guess the User Guide comes in the form of a sale item from the Microsoft Library. If you have a quick answer to this, , or you can point me in the right direction it will be much appreciated. On this point, in his reply Bob Phillips started out with a comment that "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" but that was as far as it went. Any chance of sending the complete response? Possible Code Solution: Private Sub Worksheet_Change(ByVal Target As Range) 'Presume the argument is the current cell being entered Const WS_RANGE As String = "a1" 'Flags cell to which sub is to apply; On Error GoTo ws_exit: 'Error handling routine Application.EnableEvents = False 'Temporary suspension of all other events If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then ' If Target is flagged cell then With Target Sheetn.name = target.text 'See comment below End With End If ws_exit: Application.EnableEvents = True 'Resume normal processing of all other events End Sub Comment As written, the code handles a single 'cell/detail worksheet' OK, but lets say there are 6 branches which gives rise to a problem of linking incoming cell to respective worksheet. One way might be to: Defining WS_RANGE1, WS_RANGE2, ... WS_RANGE6 Compare with incoming Target If match set flag 1 to 6 Have separate naming statements for each flag Reset flag to zero Will appreciate all suggestions. Thank You Ian Campbell Sydney, Australia |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking worksheet tab 2, 3, 4 ... names to cells in worksheet 1
To access the standard code module you can:
1- Press Alt+F11 2- Click ToolsMacroVisual Basic Editor To access the Sheet code module you can: 1- Right click the sheet tab, then click or select "view code" from the menu box. 2- Press Alt+F11, in the left panel of the VBE, in the Project window, double click the sheet you want to open the code window on. In the standard code window, if it is darkened, on the VBE menu bar, click InsertModule and you should see Module 1 appear in the Project window in the left panel. "northside104" wrote: Situation: Regional HQ is provided with template workbook for reporting activity of their branches. Worksheet 1 is the region summary of worksheets 2, 3, 4, ... which hold the individual branch details. When setting up Regions change the name of the branch column headings on worksheet 1 from "Branch n" to the location name. It is highly desirable that at the same time as a branch column heading is changed, the name tab of worksheet n+1 is also changed. My Challenges: I have read several posts on this subject (Bob Phillips & Gert - 12/9/2006, Dave Peterson & Big Rick - 1/14/2006) and have a reasonable understanding of the code solution, subject to some questions below. The big challenge is how to drop the code into the worksheet! Can't find any guidance in the Excel Help/VB Reference - guess the User Guide comes in the form of a sale item from the Microsoft Library. If you have a quick answer to this, , or you can point me in the right direction it will be much appreciated. On this point, in his reply Bob Phillips started out with a comment that "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" but that was as far as it went. Any chance of sending the complete response? Possible Code Solution: Private Sub Worksheet_Change(ByVal Target As Range) 'Presume the argument is the current cell being entered Const WS_RANGE As String = "a1" 'Flags cell to which sub is to apply; On Error GoTo ws_exit: 'Error handling routine Application.EnableEvents = False 'Temporary suspension of all other events If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then ' If Target is flagged cell then With Target Sheetn.name = target.text 'See comment below End With End If ws_exit: Application.EnableEvents = True 'Resume normal processing of all other events End Sub Comment As written, the code handles a single 'cell/detail worksheet' OK, but lets say there are 6 branches which gives rise to a problem of linking incoming cell to respective worksheet. One way might be to: Defining WS_RANGE1, WS_RANGE2, ... WS_RANGE6 Compare with incoming Target If match set flag 1 to 6 Have separate naming statements for each flag Reset flag to zero Will appreciate all suggestions. Thank You Ian Campbell Sydney, Australia |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking Tab/worksheet names to a worksheet cell | Excel Discussion (Misc queries) | |||
Linking Tab Names to a single cell with a worksheet | Excel Programming | |||
Linking cells by name only to another worksheet.... | Excel Worksheet Functions | |||
Linking arrays and Worksheet names | Excel Worksheet Functions | |||
Linking cells to another worksheet | Excel Programming |