Run macro on exit

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I have a multi-sheet document. Before I exit I like to click box A1 in all
sheets so that when I open the document everything is all neat and tidy and I
know that I am at the very start of the sheet.

Is there a way to do this automatically?
Perhaps a macro that runs "on exit" ?
 
Set the security level to low/medium in (Tools|Macro|Security). From workbook
press Alt+F11 to launch VBE (Visual Basic Editor). From the left treeview
search for the workbook name and click on + to expand it. Within that you
should see the following

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
This Workbook

Double click 'This WorkBook' and paste the below code to the right code pane.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
For Each ws In Sheets
ws.Activate: ws.Range("A1").Activate
Next
Sheets(1).Activate
Application.ScreenUpdating = True
End Sub

If this post helps click Yes
 
Suggest you do it Automatically on open instead. Just rename the macro
you use to Auto_Open .
 
Thank you, Jacob. Your instructions were clear and I was able to paste the
code where you indicated. I saved and closed the VBE, but the macro did not
seem to work. I even went to the "Trust Center" (Excel 2007) and ENABLED all
macros just to see if security might have been the problem. But it wasn't.

I opened the document, clicked a random cell in a random sheet, saved, and
then closed the document (red X top right of window). When opened again the
selected cell was still selected.

Should I be doing somehthing differently?
 
The problem is that I am posting this workbook on the internet. I want the
person who opens the doc to see all the purty little A1 cells selected.
 
And since the auto_Open procedure will run when the user opens the workbook
(assuming that macros are allowed to run), they'll see what you want.

On the other hand, if you do the same thing when the user closes the workbook,
then you'll have to have your code save those changes or rely on the user to
save when prompted.

If the user made other changes that he/she didn't want to save, your code would
save those changes.

If you relied on the user, he/she may not save (by accident) or may not save
because he/she didn't want to for a real reason.

If you expect that the user will update/save the copy retrieved from the
internet, I'd reconsider that decision.

If you don't expect the user to update/save that changes, then I'd have a
dedicated macro that selected those cells. I'd run it whenever I wanted and
before I was ready to publish to the internet. I wouldn't make it automatic.
 
Yes! Yes!, Dave. A dedicated macro. That's exactly what I want to do!

Your arguments are logical and I agree whole heartedly.
I had just not thought it out to that degree . . .

The users will not be modifying the document. Just opening and printing.
Some are bare beginners and have indicated a reluctance to open a document
with macros enabled because of the dire-sounding Microsoft warnings.

So your suggestion is perfect! I'll just run the macro before saving and
closing.
The user will never need to enable macros.

Ummm . . . . er. . . . (now all I need is a small bit of macro code . . .
.. )

I've definitely clicked "Yes" your post was helpful.
 
Same code as you were given by Jacob only change the name to MySheetView or
similar.

from Private Sub Workbook_BeforeClose(Cancel As Boolean)

to Sub MySheetView()

Place it in a general module.

Assign the macro to a button for users to click.

You will still get the dire warnings no matter which code route you take.


Gord Dibben MS Excel MVP
 
Another option is to put that macro in a different workbook. Then you could
open that macro workbook whenever you wanted to do this to any workbook.

You won't have to share the macro with others and you won't have to duplicate
the procedure in other workbooks.

Lots of people use their Personal.xls workbook for this kind of thing. And they
store that personal.xls file in their XLStart folder.

Then whenever you needed the macro, you'd make sure the workbook to fix was
active and then hit alt-f8. Choose the macro you want to run (MySheetView using
Gord's naming scheme) and run it.

This would go in that General module (not behind a worksheet and not behind
ThisWorkbook):

Option Explicit
Sub MySheetView()

Dim wks As Worksheet
Dim CurSh As Object 'could be a worksheet or a chartsheet or ...

Set CurSh = ActiveSheet

For Each wks In ActiveWorkbook.Worksheets
If wks.Visible = xlSheetVisible Then
Application.Goto wks.Range("A1"), Scroll:=True
End If
Next wks

CurSh.Select

End Sub
 
ps.

By storing the workbook in your XLStart folder, excel will open it each time you
open excel.
 
Okay, Gord, Thanks. Got it.
And the macro works!
But I don't know how to assign it a button.
I am presently clicking on Developer / Macros / MySheetView
and then clicking Run.
Not sure what a general module is, either. (Sorry. I'm fairly new at this,
myself.)

BTW, when I re-insert the original line of code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
I can no longer see the macro in the Developer / Macros list.
And, unfortunately, it still does not work when I close the program.
I'm sure I'm doing something wrong due to inexperience.

Regarding macros, actually the dire warnings are not what bothers the users.
It's the having to enable those macros that makes them uncomfortable.
They are just fine with the warnings as long as the macros stay disabled.
(This will eventually change as they get more familiar with all this stuff :-)

I have clicked the Yes, your post answered my question.
 
I like the separate Personal.xls workbook idea.
I can keep all my other .xls documents macro-free.

Please tell me where to find my XLStart folder.

I have pasted your code into a VBE window labeled "Test.xls - Module1 (Code)"

The other code is in a window labeled "Test.xls - ThisWorkbood (Code)"

Have I got that right?

I have clicked "yes" on your post also.
Thank you all for all your help.
 
You can find the location of your XLStart folder by
opening excel
Opening your test.xls workbook (might as well do it now)
Open the VBE (alt-f11 is a quick way)
Show the immediate window (ctrl-g)
type this and hit enter:
?application.startuppath

For me (xl2003 and winXP Home), it's:
C:\Documents and Settings\(username)\Application Data\Microsoft\Excel\XLSTART

Make a note of that path.

Now back to excel

(It sounds like you put the code in the correct spot.)

File|SaveAs (in xl2003 menus)

Save this file as personal.xls in that folder

But you're not done.

Now hide the window so that the macros will still be available, but the workbook
won't be visible in excel.
In xl2003 menus:
Window|Hide window

Now close excel.

You'll be prompted to save the change to personal.xls (hiding the window counts
as a change). Answer yes.

And reopen excel.

Open a test workbook.

Hit alt-F8 to see if you see your macro(s) listed.

And select one and run it (if you want).

=========
And you may want to make a change to your code:

Option Explicit
Sub MySheetView()

Dim wks As Worksheet
Dim CurSh As Object 'could be a worksheet or a chartsheet or ...

Set CurSh = ActiveSheet

Application.screenupdating = false

For Each wks In ActiveWorkbook.Worksheets
If wks.Visible = xlSheetVisible Then
Application.Goto wks.Range("A1"), Scroll:=True
End If
Next wks

CurSh.Select

Application.screenupdating = true

End Sub

Those two additional .screenupdating lines will hide the flickering screen when
you run the macro.

========
After you've done all this stuff

Close excel
Open windows explorer
Traverse to that XLStart folder
Copy your personal.xls file and save it to a nice location.

Backups are important.

And someday, you're going to mean to create one macro based on an older one and
instead, you'll just update the wrong one. You'll be happy that you have a
backup!
 
Back
Top