Open event failing

[Spike]

I have a workbook with various macros, class modules etc. Everything has
worked fine until recently when the “Open†event macro does not fire up
anymore.. Also a macro that refers to named ranges fails as the named ranges
are no longer recognised; if this macro is stopped (not paused) then the
named ranges show on the relevant spreadsheet.

The Vba compiles ok and all relevant References are ticked in Tools\Refs.

If anyone has any ideas I will be very pleased to hear as I am loathe to
rebuild the complete workbook. I guess the answer is very basic but it has
eluded me to date!

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Barb Reinhardt]

Have you checked the following which I presume you know, but I'm listing them
anyway

1) The macros are enabled when the workbook is opened. If they aren't
enabled, they don't run.
2) Application.EnableEvents is set to True. You can reset this by closing
and reopening Excel, or in the immedicate window, enter

Application.EnableEvents = True

Then try to open your workbook again.

 

[Project Mangler]

If the problem is just with one workbook it might be worth trying Code
Cleaner

http://www.appspro.com/Utilities/CodeCleaner.htm

This is a quote from the site:

"During the process of creating VBA programs a lot of junk code builds up in
your files. If you don't clean your files periodically you will begin to
experience strange problems caused by this extra baggage"

(with thanks to Chip Pearson whom I first saw mention this utility back in
February)

HTH

 

[Spike]

Many thanks for your advice.

if you mean Macro Security; then this is set to low so the macros are
enabled . Other macros (triggered by buttons) work once the workbook is
opened but there is this odd business of the named ranges not being
recognised.

I have not tried the Application.EnableEvents set to true and will certainly
try this. where should i place this piece of code? As the open event is not
working does not seem logical to place it there.

 

[Jim Cone]

Are you opening the workbook using a keyboard shortcut that includes the Shift key?
Macros are disabled on opening if the Shift key is down.
--
Jim Cone
Portland, Oregon USA
Custom Sorting... http://www.contextures.com/excel-sort-addin.html




"Spike" <[email protected]>
wrote in message
I have a workbook with various macros, class modules etc. Everything has
worked fine until recently when the “Open†event macro does not fire up
anymore.. Also a macro that refers to named ranges fails as the named ranges
are no longer recognised; if this macro is stopped (not paused) then the
named ranges show on the relevant spreadsheet.

The Vba compiles ok and all relevant References are ticked in Tools\Refs.

If anyone has any ideas I will be very pleased to hear as I am loathe to
rebuild the complete workbook. I guess the answer is very basic but it has
eluded me to date!

 

[Spike]

Thanks for the offer Don but i will give it a good going over tomorrow and
see if App.EnableEvents works. Having thought about it, i have recently
added some code so i guess this is somehow causing a conflict so prob best i
go back to an earlier version and start again

 

[JLatham]

Spike,
If macros are actually not running, putting Application.EnableEvents=True
anywhere is a futile effort, it won't ever be seen.

Barb was probably referring going into the VBEditor at some point and typing
that instruction and pressing [Enter] to ensure that envent processing is
taking place. But as she said, saving the workbook then closing and
reopening Excel should automatically (re)enable them.

To test if the Workbook_Open() event is working at all, put
STOP
as the very first statement in its code and then close and reopen the
workbook and see if it hits that Stop. If it does, then use [F8] to single
step through the rest of the code and perhaps determine where it seems to be
failing.

Make sure your Workbook_Open() event is actually in the ThisWorkbook code
module.