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

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