Preventing workbook_open code executing.

[GeraldM]

Does anyone know how I can normally open a spreadsheet and execute
workbook_open code and other times open without executing workbook_open code?

I have tried holding the shift key when opening but the workbook_open code
still executes.

I am using Excel 2007 12.0.6504.5001 SP2 MSO 12.0.6425.1000

Some Background:
-------------------
I use spreadsheets as a powerful and flexible means of reporting data
contained in an SQL database. I save the spreadsheets as an XLT.

When the spreadsheet is opened, I use workbook_open code to pre-load data
from SQL that is used as combo-box list sources - and set other initial
values. The user selects from the combo-box then further data is retrieved
that relates to the combo-box selection.

When I want to change any macro logic I don't want the pre-loads etc to
happen. So I right-click and open the spreadsheet as an XLT. I have
incorporated code in the workbook_open routine so that if the spreadsheet is
opened as an XLT then the routine is exited and the pre-load data does not
load and initial values are not set. All works well.

My Problem is when the spreadsheet is opened from a hyperlink :
----------------------------------------------------------------
1) In this case the spreadsheet opens as an XLT and the pre-loading and
initial setting don't happen.

2) If I save the spreadsheet as an XLS then the pre-loads etc happen, BUT
when I need to change macro code, then I know of no way to prevent the
pre-loads.

 

[Jacob Skaria]

Hi Gerald

Try opening the file from menu File>Open (holding the shift key). If you
open the file from Explorer it will not work.

OR using code

Application.EnableEvents = False
'Open the workbook
Application.EnableEvents = True

If this post helps click Yes

 

[GeraldM]

Thanks Jacob:

Opening with file>open (with shift key) does work.

It's a bit clumsy I feel. Pity there is no right-click open alternative -
would be useful when there are a number of spreadsheets to open.

Cannot use the code method because to disable the events means I first have
to open the spreadsheet - then that would have already executed the code to
pre-load the data - which is something I want to avoid.