| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open event failing
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! -- with kind regards Spike |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Open event failing
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. -- HTH, Barb Reinhardt "Spike" wrote: 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! -- with kind regards Spike |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Open event failing
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" 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! -- with kind regards Spike |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Open event failing
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. -- with kind regards Spike "Barb Reinhardt" wrote: 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. -- HTH, Barb Reinhardt "Spike" wrote: 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! -- with kind regards Spike |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Open event failing
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" 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! -- with kind regards Spike |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Open event failing
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 -- with kind regards Spike "Don Guillett" wrote: 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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Spike" 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! -- with kind regards Spike . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Open event failing
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. "Spike" wrote: 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. -- with kind regards Spike "Barb Reinhardt" wrote: 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. -- HTH, Barb Reinhardt "Spike" wrote: 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! -- with kind regards Spike |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Open Event | Excel Discussion (Misc queries) | |||
| Refresh Query on open failing | Excel Programming | |||
| event failing | Excel Programming | |||
| user form-on open event? keydown event? | Excel Programming | |||
| Userform_Initialize event failing to be called | Excel Programming | |||
Linear Mode
