Need Before_Save code
IT WORKS PERFECTLY!!! Thanks guys for taking so much time
with this. One thing about this code - it's so easily
transportable to any workbook. Again, thank you. Phil
-----Original Message-----
Trevor,
Yep, that'll do it.
Was concentrating on using the sheet names theat the OP
was using
but for what he wanted to do, cycling through all sheets
the way you
suggest is better.
The only named sheet that needed to be referenced was the
one that
he wanted visible all the time.
John
Trevor Shuttleworth wrote:
John
a concise way ? ;-) Sure there's better (and probably
shorter)
Sub Auto_Open()
Application.ScreenUpdating = False
For Each ws In Worksheets
ws.Select
Application.Goto ws.Range("A1"), True
Next
Worksheets("Scorecard").Select
Application.ScreenUpdating = True
End Sub
Regards
Trevor
"John Wilson" wrote in message
...
Phil,
Now I think I see what you're after.
The following is the long way of doing this (I'm sure
there's
a more concise way), but it should do what you want:
Sub Auto_Open()
Application.ScreenUpdating = False
Worksheets("Employee").Activate
Range("A1").Activate
Application.Goto ActiveCell, True
Worksheets("Customer").Activate
Range("A1").Activate
Application.Goto ActiveCell, True
Worksheets("Finance").Activate
Range("A1").Activate
Application.Goto ActiveCell, True
Worksheets("Scorecard").Activate
Range("A1").Activate
Application.Goto ActiveCell, True
Application.ScreenUpdating = True
End Sub
John
Phil Hageman wrote:
Trevpr and John,
Used the Array code - it puts the cursor in A1,
and "Scorecard" is the active worksheet, but in all
worksheets, the scroll does not put the document
where A1
is visible. To test, I put the cursor at the end
of each
worksheet - that is what is visible when opening.
Is there a scroll instruction to use?
Phil
-----Original Message-----
Or:
Sub Auto_Open()
Application.ScreenUpdating = False
Worksheets(Array
("Employee", "Customer", "Finance", "Scorecard")).Select
Range("A1").Select
Worksheets("Scorecard").Select
Application.ScreenUpdating = True
End Sub
Regards
Trevor
"John Wilson" wrote in
message
...
Phil,
If, as you say, you want a standard set-up when
the user
"opens" the workbook, a simple way would be as
follows:
Sub Auto_Open()
Application.ScreenUpdating = False
Worksheets("Employee").Activate
Range("A1").Activate
Worksheets("Customer").Activate
Range("A1").Activate
Worksheets("Finance").Activate
Range("A1").Activate
Worksheets("Scorecard").Activate
Range("A1").Activate
Application.ScreenUpdating = True
End Sub
The above macro, when placed in a regular
module, will
run whenever
the workbook is opened and insure
that "Scorecard" is
the active sheet
and that cell "A1" is the active cell on all the
sheets.
John
Phil Hageman wrote:
My workbook is named Scorecard, and its
worksheets are
entitled Scorecard, Customer, Finance, and
Employee.
When the user clicks the Save icon, I want the
cursor
in
each worksheet to be placed in Cell A1, and the
worksheet
named Scorecard to be the the last worksheet
changed.
The object is that when a another user first
opens the
Workbook, the worksheet named Scorecard comes
up
first,
and as each of the other worksheets is opened,
it
shows
the top of the worksheet - thus the force of
cursor to
cell A1.
Thanks, Phil
.
.
|