issue when opening a workbook through a macro shortcut

[DanC]

Hi, I tried searching the group, but didn't find anything matching my
problem. It seems like something that would've been noticed before,
but maybe I'm just not using the right search terms. This isn't a
huge concern, but I'd like to know if I'm misunderstanding something
about how VBA works, as I'm pretty new to it. Thanks in advance!


I have a file (forms.xls) that I want to be able to open on a
keypress, so I've added a macro bound to Ctrl+Shift+F that just opens
forms.xls. This file is a series of data entry forms that I have to
print out, but not save (travel reimbursements, check requests,
etc.). Since I got tired of telling Excel that I don't want to save
changes, I added the following code to ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved = True
End Sub

While this works perfectly when I open the file normally, for some
reason the "Do you want to save changes?" box appears if I've opened
the file using Ctrl+Shift+F. Why is this, and is there any way to get
rid of it for this file?

Thanks again.

--DanC

 

[Dave Peterson]

Does the problem go away if you drop the shift from the shortcut key
combination?

Holding the shiftkey when you open a workbook stops the open macros from
running. It also confuses excel/vba into thinking it should stop.

And I'm guessing that you may have turned off events and since the code is
stopping, events aren't getting turned back on and so the _beforeclose event is
never firing.

If you try again (with the shiftkey), you can go into the VBE and type this into
the immediate window:

?application.enableevents

If you see False, then that's the problem.

If you see True, then my guess is wrong.

 

[DanC]

Changing the shortcut key did the trick. Thanks for the tip!

Strangely, I checked whether events were enabled before changing
shortcuts and it said they were. So, I'm not quite sure why it wasn't
working (as it seems events were enabled), but it works now, so I'm
happy.

Thanks again!

--DanC

 

[Dave Peterson]

If you have a workbook that has a workbook_open event or an auto_open procedure,
then if you open that file with the shift key held down, you stop those
procedures from running.

The shift-key in the shortcut combination confuses excel/vba to stop after you
open a file using one of those shortcut keys.