G
Guest
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
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