Preventing workbook_open code executing.
 

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.

Preventing workbook_open code executing.
 

Hi Gerald

Try opening the file from menu FileOpen (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
---------------
Jacob Skaria


"GeraldM" wrote:

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.

Preventing workbook_open code executing.
 

Thanks Jacob:

Opening with fileopen (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.

"Jacob Skaria" wrote:

Hi Gerald

Try opening the file from menu FileOpen (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
---------------
Jacob Skaria


"GeraldM" wrote:

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.