Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_open not running and Phantom Projects
I recently discovered that I have the problem of workbook_open not
firing upon subsequent openings of my workbook....e.g. start excel and open workbook...workbook_open runs fine. Close workbook and re-open it, workbook_open does not run. I also discovered that when this happens I have the phantom project problem....e.g. VBA project does not close when workbook is closed and a 2nd project is opened when the workbook is re-opened. I have searched the forum and read most of everything on these two issues. I believe they are related and the workbook_open not running is probably caused by the project not closing. Problem is I still don't know what to do about it. This happens for an app that I have been working on and enhancing for the past few years. Happens on 2 separate computers running XP with excel 2003 and 2000. Now, I can create a new workbook and put a msgbox in the workbook_open and it works all the time. I have tested my app through all the dev versions and found the version where this starts happening....e.g., problem does not happen for all versions earlier than this and happens for all versions thereafter. The differences between the good version and bad version are small.....just 3k diff in file size and about 40 lines of bland VB code. All changes were contained in 2 different modules (23 modules in total). If I swap the modules the problem does not follow it. If I remove these modules entirely the problem does not go away. So, I started removing modules 1 by 1 and found a module whose presence seems to cause the problem....e.g. once that particular module was removed, the problem went away. But, this module is identical to the one in previous versions! The workbook_open just makes a sheet current, reads data from a cell and then displays a splash screen/userform. nothing fancy or tricky. So, I am stumped and don;t know where to go. As others have reported, If Excel is closed and restarted, the problem is cleared.....But I'd like to solve it and seeing that I have 2 very similar workbooks it seems that I should be able to find out what is causing it. Can anyone out there give me some guidance here? At this point I don't know what to look for. thanks chuck |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_open not running and Phantom Projects
Instead of closing the workbook at the end of the macro save the workboo and
then do an apllication.quit. The close may not be shuting down the application the quit should kill the application. "chuckm" wrote: I recently discovered that I have the problem of workbook_open not firing upon subsequent openings of my workbook....e.g. start excel and open workbook...workbook_open runs fine. Close workbook and re-open it, workbook_open does not run. I also discovered that when this happens I have the phantom project problem....e.g. VBA project does not close when workbook is closed and a 2nd project is opened when the workbook is re-opened. I have searched the forum and read most of everything on these two issues. I believe they are related and the workbook_open not running is probably caused by the project not closing. Problem is I still don't know what to do about it. This happens for an app that I have been working on and enhancing for the past few years. Happens on 2 separate computers running XP with excel 2003 and 2000. Now, I can create a new workbook and put a msgbox in the workbook_open and it works all the time. I have tested my app through all the dev versions and found the version where this starts happening....e.g., problem does not happen for all versions earlier than this and happens for all versions thereafter. The differences between the good version and bad version are small.....just 3k diff in file size and about 40 lines of bland VB code. All changes were contained in 2 different modules (23 modules in total). If I swap the modules the problem does not follow it. If I remove these modules entirely the problem does not go away. So, I started removing modules 1 by 1 and found a module whose presence seems to cause the problem....e.g. once that particular module was removed, the problem went away. But, this module is identical to the one in previous versions! The workbook_open just makes a sheet current, reads data from a cell and then displays a splash screen/userform. nothing fancy or tricky. So, I am stumped and don;t know where to go. As others have reported, If Excel is closed and restarted, the problem is cleared.....But I'd like to solve it and seeing that I have 2 very similar workbooks it seems that I should be able to find out what is causing it. Can anyone out there give me some guidance here? At this point I don't know what to look for. thanks chuck |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_open not running and Phantom Projects
If you've read everything about Phantom projects (I think I may have coined
the term) you will have read Jon Peltier's conclusion that the issue may linked to certain third party VSTO addins. I doubt the failure wb Open events is directly related, though conceivably the same addin (if you have one) is responsible both for the phantom projects and for disabling app' events, albeit for different reasons. However you also say the problem has only occurred since modifying your own code so maybe suspicion lays there. Quick thing to try, in the immediate window (ctrl-g), paste ?application.EnableEvents Before and after opening a workbook that you expect an open event to run, place the cursor at the end of the line and hit enter. If EnableEvents are enabled before and after the open event really should have fired. Maybe for some reason the code terminates prematurely giving the impression it failed, add the following debug lines in the open event ' start of code debug.? thisworkbook.name, "open: start" 'code ' end of code debug.? thisworkbook.name, "open: all done" End Sub Regards, Peter T "chuckm" wrote in message ... I recently discovered that I have the problem of workbook_open not firing upon subsequent openings of my workbook....e.g. start excel and open workbook...workbook_open runs fine. Close workbook and re-open it, workbook_open does not run. I also discovered that when this happens I have the phantom project problem....e.g. VBA project does not close when workbook is closed and a 2nd project is opened when the workbook is re-opened. I have searched the forum and read most of everything on these two issues. I believe they are related and the workbook_open not running is probably caused by the project not closing. Problem is I still don't know what to do about it. This happens for an app that I have been working on and enhancing for the past few years. Happens on 2 separate computers running XP with excel 2003 and 2000. Now, I can create a new workbook and put a msgbox in the workbook_open and it works all the time. I have tested my app through all the dev versions and found the version where this starts happening....e.g., problem does not happen for all versions earlier than this and happens for all versions thereafter. The differences between the good version and bad version are small.....just 3k diff in file size and about 40 lines of bland VB code. All changes were contained in 2 different modules (23 modules in total). If I swap the modules the problem does not follow it. If I remove these modules entirely the problem does not go away. So, I started removing modules 1 by 1 and found a module whose presence seems to cause the problem....e.g. once that particular module was removed, the problem went away. But, this module is identical to the one in previous versions! The workbook_open just makes a sheet current, reads data from a cell and then displays a splash screen/userform. nothing fancy or tricky. So, I am stumped and don;t know where to go. As others have reported, If Excel is closed and restarted, the problem is cleared.....But I'd like to solve it and seeing that I have 2 very similar workbooks it seems that I should be able to find out what is causing it. Can anyone out there give me some guidance here? At this point I don't know what to look for. thanks chuck |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_open not running and Phantom Projects
On Jul 21, 10:42 am, "Peter T" <peter_t@discussions wrote:
If you've read everything about Phantom projects (I think I may have coined the term) you will have read Jon Peltier's conclusion that the issue may linked to certain third party VSTO addins. I doubt the failure wb Open events is directly related, though conceivably the same addin (if you have one) is responsible both for the phantom projects and for disabling app' events, albeit for different reasons. However you also say the problem has only occurred since modifying your own code so maybe suspicion lays there. Quick thing to try, in the immediate window (ctrl-g), paste ?application.EnableEvents Before and after opening a workbook that you expect an open event to run, place the cursor at the end of the line and hit enter. If EnableEvents are enabled before and after the open event really should have fired. Maybe for some reason the code terminates prematurely giving the impression it failed, add the following debug lines in the open event ' start of code debug.? thisworkbook.name, "open: start" 'code ' end of code debug.? thisworkbook.name, "open: all done" End Sub Regards, Peter T "chuckm" wrote in message ... I recently discovered that I have the problem of workbook_open not firing upon subsequent openings of my workbook....e.g. start excel and open workbook...workbook_open runs fine. Close workbook and re-open it, workbook_open does not run. I also discovered that when this happens I have the phantom project problem....e.g. VBA project does not close when workbook is closed and a 2nd project is opened when the workbook is re-opened. I have searched the forum and read most of everything on these two issues. I believe they are related and the workbook_open not running is probably caused by the project not closing. Problem is I still don't know what to do about it. This happens for an app that I have been working on and enhancing for the past few years. Happens on 2 separate computers running XP with excel 2003 and 2000. Now, I can create a new workbook and put a msgbox in the workbook_open and it works all the time. I have tested my app through all the dev versions and found the version where this starts happening....e.g., problem does not happen for all versions earlier than this and happens for all versions thereafter. The differences between the good version and bad version are small.....just 3k diff in file size and about 40 lines of bland VB code. All changes were contained in 2 different modules (23 modules in total). If I swap the modules the problem does not follow it. If I remove these modules entirely the problem does not go away. So, I started removing modules 1 by 1 and found a module whose presence seems to cause the problem....e.g. once that particular module was removed, the problem went away. But, this module is identical to the one in previous versions! The workbook_open just makes a sheet current, reads data from a cell and then displays a splash screen/userform. nothing fancy or tricky. So, I am stumped and don;t know where to go. As others have reported, If Excel is closed and restarted, the problem is cleared.....But I'd like to solve it and seeing that I have 2 very similar workbooks it seems that I should be able to find out what is causing it. Can anyone out there give me some guidance here? At this point I don't know what to look for. thanks chuck Ok...I've done some more digging into the problem It now seems that teh root of the problem is when I introduced a call to a routine in workbook_open that basically executes a Application.EnableEvents = False. This was done to enable some code in the sheet to execute that had to do with row highlighting and cursor placement. so, I bring up excel and the workbook opens fine. Close the workbook and project is still open. Open workbook again, workbook_open doen not execute and phantom project is open. Now, if I issue a Application.EnableEvents = True, the phantom project goes away. I can then close the workbook, re-open and it works ok. Seems that I need to issue Application.EnableEvents = True from a workbook_close..... if such a thing exists. So, seems that the Workbook_open and Phantom projects problem are related...at least in my case. Now...is there a Workbook_close...or something like that that runs when you close the workbook? thanks everyone for you help and guidance chuck |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_open not running and Phantom Projects
Not sure I quite follow but from what you say it sounds like it's your code
that disables events and fails to reset them. If that's the case, you really should reset the events as your code is about to terminate, if necessary from within an error handler. Indeed there is a Workbook close event but if events are disabled the close event won't fire. As a final escape when all else fails try this in a normal module Sub auto_close() application.enable.events = true end sub This and auto_open are not impacted by events, maybe use same for your open event. Of course best thing is make sure you reset events. Regards, Peter T "chuckm" wrote in message ... On Jul 21, 10:42 am, "Peter T" <peter_t@discussions wrote: If you've read everything about Phantom projects (I think I may have coined the term) you will have read Jon Peltier's conclusion that the issue may linked to certain third party VSTO addins. I doubt the failure wb Open events is directly related, though conceivably the same addin (if you have one) is responsible both for the phantom projects and for disabling app' events, albeit for different reasons. However you also say the problem has only occurred since modifying your own code so maybe suspicion lays there. Quick thing to try, in the immediate window (ctrl-g), paste ?application.EnableEvents Before and after opening a workbook that you expect an open event to run, place the cursor at the end of the line and hit enter. If EnableEvents are enabled before and after the open event really should have fired. Maybe for some reason the code terminates prematurely giving the impression it failed, add the following debug lines in the open event ' start of code debug.? thisworkbook.name, "open: start" 'code ' end of code debug.? thisworkbook.name, "open: all done" End Sub Regards, Peter T "chuckm" wrote in message ... I recently discovered that I have the problem of workbook_open not firing upon subsequent openings of my workbook....e.g. start excel and open workbook...workbook_open runs fine. Close workbook and re-open it, workbook_open does not run. I also discovered that when this happens I have the phantom project problem....e.g. VBA project does not close when workbook is closed and a 2nd project is opened when the workbook is re-opened. I have searched the forum and read most of everything on these two issues. I believe they are related and the workbook_open not running is probably caused by the project not closing. Problem is I still don't know what to do about it. This happens for an app that I have been working on and enhancing for the past few years. Happens on 2 separate computers running XP with excel 2003 and 2000. Now, I can create a new workbook and put a msgbox in the workbook_open and it works all the time. I have tested my app through all the dev versions and found the version where this starts happening....e.g., problem does not happen for all versions earlier than this and happens for all versions thereafter. The differences between the good version and bad version are small.....just 3k diff in file size and about 40 lines of bland VB code. All changes were contained in 2 different modules (23 modules in total). If I swap the modules the problem does not follow it. If I remove these modules entirely the problem does not go away. So, I started removing modules 1 by 1 and found a module whose presence seems to cause the problem....e.g. once that particular module was removed, the problem went away. But, this module is identical to the one in previous versions! The workbook_open just makes a sheet current, reads data from a cell and then displays a splash screen/userform. nothing fancy or tricky. So, I am stumped and don;t know where to go. As others have reported, If Excel is closed and restarted, the problem is cleared.....But I'd like to solve it and seeing that I have 2 very similar workbooks it seems that I should be able to find out what is causing it. Can anyone out there give me some guidance here? At this point I don't know what to look for. thanks chuck Ok...I've done some more digging into the problem It now seems that teh root of the problem is when I introduced a call to a routine in workbook_open that basically executes a Application.EnableEvents = False. This was done to enable some code in the sheet to execute that had to do with row highlighting and cursor placement. so, I bring up excel and the workbook opens fine. Close the workbook and project is still open. Open workbook again, workbook_open doen not execute and phantom project is open. Now, if I issue a Application.EnableEvents = True, the phantom project goes away. I can then close the workbook, re-open and it works ok. Seems that I need to issue Application.EnableEvents = True from a workbook_close..... if such a thing exists. So, seems that the Workbook_open and Phantom projects problem are related...at least in my case. Now...is there a Workbook_close...or something like that that runs when you close the workbook? thanks everyone for you help and guidance chuck |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_open not running and Phantom Projects
On Jul 21, 2:49 pm, "Peter T" <peter_t@discussions wrote:
Not sure I quite follow but from what you say it sounds like it's your code that disables events and fails to reset them. If that's the case, you really should reset the events as your code is about to terminate, if necessary from within an error handler. Indeed there is a Workbook close event but if events are disabled the close event won't fire. As a final escape when all else fails try this in a normal module Sub auto_close() application.enable.events = true end sub This and auto_open are not impacted by events, maybe use same for your open event. Of course best thing is make sure you reset events. Regards, Peter T "chuckm" wrote in message ... On Jul 21, 10:42 am, "Peter T" <peter_t@discussions wrote: If you've read everything about Phantom projects (I think I may have coined the term) you will have read Jon Peltier's conclusion that the issue may linked to certain third party VSTO addins. I doubt the failure wb Open events is directly related, though conceivably the same addin (if you have one) is responsible both for the phantom projects and for disabling app' events, albeit for different reasons. However you also say the problem has only occurred since modifying your own code so maybe suspicion lays there. Quick thing to try, in the immediate window (ctrl-g), paste ?application.EnableEvents Before and after opening a workbook that you expect an open event to run, place the cursor at the end of the line and hit enter. If EnableEvents are enabled before and after the open event really should have fired. Maybe for some reason the code terminates prematurely giving the impression it failed, add the following debug lines in the open event ' start of code debug.? thisworkbook.name, "open: start" 'code ' end of code debug.? thisworkbook.name, "open: all done" End Sub Regards, Peter T "chuckm" wrote in message ... I recently discovered that I have the problem of workbook_open not firing upon subsequent openings of my workbook....e.g. start excel and open workbook...workbook_open runs fine. Close workbook and re-open it, workbook_open does not run. I also discovered that when this happens I have the phantom project problem....e.g. VBA project does not close when workbook is closed and a 2nd project is opened when the workbook is re-opened. I have searched the forum and read most of everything on these two issues. I believe they are related and the workbook_open not running is probably caused by the project not closing. Problem is I still don't know what to do about it. This happens for an app that I have been working on and enhancing for the past few years. Happens on 2 separate computers running XP with excel 2003 and 2000. Now, I can create a new workbook and put a msgbox in the workbook_open and it works all the time. I have tested my app through all the dev versions and found the version where this starts happening....e.g., problem does not happen for all versions earlier than this and happens for all versions thereafter. The differences between the good version and bad version are small.....just 3k diff in file size and about 40 lines of bland VB code. All changes were contained in 2 different modules (23 modules in total). If I swap the modules the problem does not follow it. If I remove these modules entirely the problem does not go away. So, I started removing modules 1 by 1 and found a module whose presence seems to cause the problem....e.g. once that particular module was removed, the problem went away. But, this module is identical to the one in previous versions! The workbook_open just makes a sheet current, reads data from a cell and then displays a splash screen/userform. nothing fancy or tricky. So, I am stumped and don;t know where to go. As others have reported, If Excel is closed and restarted, the problem is cleared.....But I'd like to solve it and seeing that I have 2 very similar workbooks it seems that I should be able to find out what is causing it. Can anyone out there give me some guidance here? At this point I don't know what to look for. thanks chuck Ok...I've done some more digging into the problem It now seems that teh root of the problem is when I introduced a call to a routine in workbook_open that basically executes a Application.EnableEvents = False. This was done to enable some code in the sheet to execute that had to do with row highlighting and cursor placement. so, I bring up excel and the workbook opens fine. Close the workbook and project is still open. Open workbook again, workbook_open doen not execute and phantom project is open. Now, if I issue a Application.EnableEvents = True, the phantom project goes away. I can then close the workbook, re-open and it works ok. Seems that I need to issue Application.EnableEvents = True from a workbook_close..... if such a thing exists. So, seems that the Workbook_open and Phantom projects problem are related...at least in my case. Now...is there a Workbook_close...or something like that that runs when you close the workbook? thanks everyone for you help and guidance chuck Thanks Peter.... I added Workbook_beforeClose and auto_close....both with Application.EnableEvents = True. That has solved my problems. appreciate it chuck |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_open not running and Phantom Projects
I added Workbook_beforeClose and auto_close....both with
Application.EnableEvents = True. That has solved my problems. It may have merely masked your problems for the time being. As I mentioned before, you really should ensure you reset EnableEvents after disabling them Regards, Peter T |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UPDATE : Phantom VBA Projects (again) | Excel Programming | |||
Phantom VBA Projects (again) | Excel Programming | |||
Phantom VBA Projects (again) | Excel Programming | |||
Running 2 projects at same time issue | Excel Programming | |||
Application.Run and VBE "phantom" projects | Excel Programming |