Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel is forced to close when run a marco???
Hi
Sorry the story may seem long winded, but I'm trying to describe it logically so the problem can be easily understood. I have added a new menu to the toolbar at the top of Excel, and assigned a macro (called "USLE") to it that opens a userform. It was all working fine until I made some changes to my VBE code, and I don't know what I've done that causes the Excel to shut when I click on my menu... Here is what happens: When I click on my menu, the Security Warning window prompts and I click "Enable Macros" as usual, the workbook then opens (but NOT the userform when it should!), and an error message prompts saying "Microsoft Office Excel has encountered a problem and needs to close", with the "Recover my work and restart Microsoft Office Excel" option checked. If I click on the recovered file in the Document Recovery panel in the left of the new Excel opened automatically, save it and close the workbook (but keep the Excel open), then click on my menu again, the userform then opens OK and everything is fine! However, if I start a new Excel application and click on my menu, the same problem happens again... Why is this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel is forced to close when run a marco???
Very difficult to tell without being able to analyze the code that throws the
error. "Sam Kuo" wrote: Hi Sorry the story may seem long winded, but I'm trying to describe it logically so the problem can be easily understood. I have added a new menu to the toolbar at the top of Excel, and assigned a macro (called "USLE") to it that opens a userform. It was all working fine until I made some changes to my VBE code, and I don't know what I've done that causes the Excel to shut when I click on my menu... Here is what happens: When I click on my menu, the Security Warning window prompts and I click "Enable Macros" as usual, the workbook then opens (but NOT the userform when it should!), and an error message prompts saying "Microsoft Office Excel has encountered a problem and needs to close", with the "Recover my work and restart Microsoft Office Excel" option checked. If I click on the recovered file in the Document Recovery panel in the left of the new Excel opened automatically, save it and close the workbook (but keep the Excel open), then click on my menu again, the userform then opens OK and everything is fine! However, if I start a new Excel application and click on my menu, the same problem happens again... Why is this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel is forced to close when run a marco???
a couple of thoughts:
are you using a menu in one workbook to open another workbook? if so, open the 2nd workbook manually and see if it works.. if the form is set to run on open: 1. hold the shift key while you open the 2nd workbook so excel opens in safe mode (or if you're running 3003 and installed in the default location, type this in the run box: "C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE" /s .. then look at your form code. if it's ok, export the form, delete it. open the workbook and import the form and see what happens. -- Gary "Sam Kuo" wrote in message ... Hi Sorry the story may seem long winded, but I'm trying to describe it logically so the problem can be easily understood. I have added a new menu to the toolbar at the top of Excel, and assigned a macro (called "USLE") to it that opens a userform. It was all working fine until I made some changes to my VBE code, and I don't know what I've done that causes the Excel to shut when I click on my menu... Here is what happens: When I click on my menu, the Security Warning window prompts and I click "Enable Macros" as usual, the workbook then opens (but NOT the userform when it should!), and an error message prompts saying "Microsoft Office Excel has encountered a problem and needs to close", with the "Recover my work and restart Microsoft Office Excel" option checked. If I click on the recovered file in the Document Recovery panel in the left of the new Excel opened automatically, save it and close the workbook (but keep the Excel open), then click on my menu again, the userform then opens OK and everything is fine! However, if I start a new Excel application and click on my menu, the same problem happens again... Why is this? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel is forced to close when run a marco???
Hi Gary
The user might or might not have a workbook open already before open my userform by clicking my menu. But in a typical situation, the user would start the Excel application, which by default opens with a new workbook, Book1, as normal (or they might have some workbooks they're working in open already). They may then click on my menu in the toolbar to open my userform. So I guess this fits what you said about "using a menu in one workbook to open another workbook"? I've just tried your suggestion to hold the Shift key while clicking on my menu with Book1 open. This opens my userform OK, but Book1 now appears above my workbook (i.e. Book1 becomes the current workbook) and whatever I do in my userform that has a reference to my workbook throws a run-time error "9: Subscript out of range". I guess I need a line to make my workbook current when my userform open to solve this problem? And if so, can you please show me how? I'm using 2003, but where would I find the run box you mentioned and what does it do? Thanks for your patient. Sam "Gary Keramidas" wrote: a couple of thoughts: are you using a menu in one workbook to open another workbook? if so, open the 2nd workbook manually and see if it works.. if the form is set to run on open: 1. hold the shift key while you open the 2nd workbook so excel opens in safe mode (or if you're running 3003 and installed in the default location, type this in the run box: "C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE" /s .. then look at your form code. if it's ok, export the form, delete it. open the workbook and import the form and see what happens. -- Gary "Sam Kuo" wrote in message ... Hi Sorry the story may seem long winded, but I'm trying to describe it logically so the problem can be easily understood. I have added a new menu to the toolbar at the top of Excel, and assigned a macro (called "USLE") to it that opens a userform. It was all working fine until I made some changes to my VBE code, and I don't know what I've done that causes the Excel to shut when I click on my menu... Here is what happens: When I click on my menu, the Security Warning window prompts and I click "Enable Macros" as usual, the workbook then opens (but NOT the userform when it should!), and an error message prompts saying "Microsoft Office Excel has encountered a problem and needs to close", with the "Recover my work and restart Microsoft Office Excel" option checked. If I click on the recovered file in the Document Recovery panel in the left of the new Excel opened automatically, save it and close the workbook (but keep the Excel open), then click on my menu again, the userform then opens OK and everything is fine! However, if I start a new Excel application and click on my menu, the same problem happens again... Why is this? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel is forced to close when run a marco???
I got a problem same with you....
did you solved it? if yes...please let me know how to do this thank you Aom "Sam Kuo" wrote: Hi Gary The user might or might not have a workbook open already before open my userform by clicking my menu. But in a typical situation, the user would start the Excel application, which by default opens with a new workbook, Book1, as normal (or they might have some workbooks they're working in open already). They may then click on my menu in the toolbar to open my userform. So I guess this fits what you said about "using a menu in one workbook to open another workbook"? I've just tried your suggestion to hold the Shift key while clicking on my menu with Book1 open. This opens my userform OK, but Book1 now appears above my workbook (i.e. Book1 becomes the current workbook) and whatever I do in my userform that has a reference to my workbook throws a run-time error "9: Subscript out of range". I guess I need a line to make my workbook current when my userform open to solve this problem? And if so, can you please show me how? I'm using 2003, but where would I find the run box you mentioned and what does it do? Thanks for your patient. Sam "Gary Keramidas" wrote: a couple of thoughts: are you using a menu in one workbook to open another workbook? if so, open the 2nd workbook manually and see if it works.. if the form is set to run on open: 1. hold the shift key while you open the 2nd workbook so excel opens in safe mode (or if you're running 3003 and installed in the default location, type this in the run box: "C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE" /s .. then look at your form code. if it's ok, export the form, delete it. open the workbook and import the form and see what happens. -- Gary "Sam Kuo" wrote in message ... Hi Sorry the story may seem long winded, but I'm trying to describe it logically so the problem can be easily understood. I have added a new menu to the toolbar at the top of Excel, and assigned a macro (called "USLE") to it that opens a userform. It was all working fine until I made some changes to my VBE code, and I don't know what I've done that causes the Excel to shut when I click on my menu... Here is what happens: When I click on my menu, the Security Warning window prompts and I click "Enable Macros" as usual, the workbook then opens (but NOT the userform when it should!), and an error message prompts saying "Microsoft Office Excel has encountered a problem and needs to close", with the "Recover my work and restart Microsoft Office Excel" option checked. If I click on the recovered file in the Document Recovery panel in the left of the new Excel opened automatically, save it and close the workbook (but keep the Excel open), then click on my menu again, the userform then opens OK and everything is fine! However, if I start a new Excel application and click on my menu, the same problem happens again... Why is this? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel is forced to close when run a marco???
Hi Aom
I think I managed to solve that problem by fixing my other problem with the troublesome UserForm. Here is what Dave Peterson suggested. You basically export the userform elsewhere, then delete the userform from your troublesome workbook, then import the exported userform back to the workbook. But I don't know if that'll fix the problem in your case: "Dave Peterson" wrote: Another thing to try against a copy of the troublesome workbook. Start a new workbook (just a temporary workbook). Open a copy of that troublesome workbook. Go into the VBE. Show the project explorer (ctrl-r if it isn't visible) Drag the userform from the troublesome workbook project to the temporary workbook project. Does it work ok there? If yes, try deleting the old userform in the troublesome workbook project. Drag the userform from the temporary workbook project back to the original workbook project. Does it work ok there? If yes, save the copy and use that instead. "aom" wrote: I got a problem same with you.... did you solved it? if yes...please let me know how to do this thank you Aom "Sam Kuo" wrote: Hi Gary The user might or might not have a workbook open already before open my userform by clicking my menu. But in a typical situation, the user would start the Excel application, which by default opens with a new workbook, Book1, as normal (or they might have some workbooks they're working in open already). They may then click on my menu in the toolbar to open my userform. So I guess this fits what you said about "using a menu in one workbook to open another workbook"? I've just tried your suggestion to hold the Shift key while clicking on my menu with Book1 open. This opens my userform OK, but Book1 now appears above my workbook (i.e. Book1 becomes the current workbook) and whatever I do in my userform that has a reference to my workbook throws a run-time error "9: Subscript out of range". I guess I need a line to make my workbook current when my userform open to solve this problem? And if so, can you please show me how? I'm using 2003, but where would I find the run box you mentioned and what does it do? Thanks for your patient. Sam "Gary Keramidas" wrote: a couple of thoughts: are you using a menu in one workbook to open another workbook? if so, open the 2nd workbook manually and see if it works.. if the form is set to run on open: 1. hold the shift key while you open the 2nd workbook so excel opens in safe mode (or if you're running 3003 and installed in the default location, type this in the run box: "C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE" /s .. then look at your form code. if it's ok, export the form, delete it. open the workbook and import the form and see what happens. -- Gary "Sam Kuo" wrote in message ... Hi Sorry the story may seem long winded, but I'm trying to describe it logically so the problem can be easily understood. I have added a new menu to the toolbar at the top of Excel, and assigned a macro (called "USLE") to it that opens a userform. It was all working fine until I made some changes to my VBE code, and I don't know what I've done that causes the Excel to shut when I click on my menu... Here is what happens: When I click on my menu, the Security Warning window prompts and I click "Enable Macros" as usual, the workbook then opens (but NOT the userform when it should!), and an error message prompts saying "Microsoft Office Excel has encountered a problem and needs to close", with the "Recover my work and restart Microsoft Office Excel" option checked. If I click on the recovered file in the Document Recovery panel in the left of the new Excel opened automatically, save it and close the workbook (but keep the Excel open), then click on my menu again, the userform then opens OK and everything is fine! However, if I start a new Excel application and click on my menu, the same problem happens again... Why is this? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel is forced to close when run a marco???
Thanks Sam Kuo ^^ my Excel show something like this.... Microsoft Office Excel has encountered a problem and needs to close. We are sorry for the inconvenience. The information you were working on might be lost. Microsoft Office Excel can try to recover it for you. [X] Recover my work and restart Microsoft Office Excel Please tell Microsoft about this problem. We have created an error report that you can send to help us improve Microsoft Office Excel. We will treat this report as confidential and anonymous. [Send Error Report] [Don't Send] for Dave Peterson suggested...I can not followed his suggestion ...I just be a user...can you explain for me step by step? Thank you very much aom "Sam Kuo" wrote: Hi Aom I think I managed to solve that problem by fixing my other problem with the troublesome UserForm. Here is what Dave Peterson suggested. You basically export the userform elsewhere, then delete the userform from your troublesome workbook, then import the exported userform back to the workbook. But I don't know if that'll fix the problem in your case: "Dave Peterson" wrote: Another thing to try against a copy of the troublesome workbook. Start a new workbook (just a temporary workbook). Open a copy of that troublesome workbook. Go into the VBE. Show the project explorer (ctrl-r if it isn't visible) Drag the userform from the troublesome workbook project to the temporary workbook project. Does it work ok there? If yes, try deleting the old userform in the troublesome workbook project. Drag the userform from the temporary workbook project back to the original workbook project. Does it work ok there? If yes, save the copy and use that instead. "aom" wrote: I got a problem same with you.... did you solved it? if yes...please let me know how to do this thank you Aom "Sam Kuo" wrote: Hi Gary The user might or might not have a workbook open already before open my userform by clicking my menu. But in a typical situation, the user would start the Excel application, which by default opens with a new workbook, Book1, as normal (or they might have some workbooks they're working in open already). They may then click on my menu in the toolbar to open my userform. So I guess this fits what you said about "using a menu in one workbook to open another workbook"? I've just tried your suggestion to hold the Shift key while clicking on my menu with Book1 open. This opens my userform OK, but Book1 now appears above my workbook (i.e. Book1 becomes the current workbook) and whatever I do in my userform that has a reference to my workbook throws a run-time error "9: Subscript out of range". I guess I need a line to make my workbook current when my userform open to solve this problem? And if so, can you please show me how? I'm using 2003, but where would I find the run box you mentioned and what does it do? Thanks for your patient. Sam "Gary Keramidas" wrote: a couple of thoughts: are you using a menu in one workbook to open another workbook? if so, open the 2nd workbook manually and see if it works.. if the form is set to run on open: 1. hold the shift key while you open the 2nd workbook so excel opens in safe mode (or if you're running 3003 and installed in the default location, type this in the run box: "C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE" /s .. then look at your form code. if it's ok, export the form, delete it. open the workbook and import the form and see what happens. -- Gary "Sam Kuo" wrote in message ... Hi Sorry the story may seem long winded, but I'm trying to describe it logically so the problem can be easily understood. I have added a new menu to the toolbar at the top of Excel, and assigned a macro (called "USLE") to it that opens a userform. It was all working fine until I made some changes to my VBE code, and I don't know what I've done that causes the Excel to shut when I click on my menu... Here is what happens: When I click on my menu, the Security Warning window prompts and I click "Enable Macros" as usual, the workbook then opens (but NOT the userform when it should!), and an error message prompts saying "Microsoft Office Excel has encountered a problem and needs to close", with the "Recover my work and restart Microsoft Office Excel" option checked. If I click on the recovered file in the Document Recovery panel in the left of the new Excel opened automatically, save it and close the workbook (but keep the Excel open), then click on my menu again, the userform then opens OK and everything is fine! However, if I start a new Excel application and click on my menu, the same problem happens again... Why is this? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel is forced to close when run a marco???
Hi Aom
If you're only a user, I'd suggest you bring your issue to the creator of the workbook (if you can), instead of trying to fix the problem youself. Otherwise, try post a new question. I'm sure someone here can help with your situation (sorry I'm no expert in fixing Excel problems myself) Good luck. Sam "aom" wrote: Thanks Sam Kuo ^^ my Excel show something like this.... Microsoft Office Excel has encountered a problem and needs to close. We are sorry for the inconvenience. The information you were working on might be lost. Microsoft Office Excel can try to recover it for you. [X] Recover my work and restart Microsoft Office Excel Please tell Microsoft about this problem. We have created an error report that you can send to help us improve Microsoft Office Excel. We will treat this report as confidential and anonymous. [Send Error Report] [Don't Send] for Dave Peterson suggested...I can not followed his suggestion ...I just be a user...can you explain for me step by step? Thank you very much aom "Sam Kuo" wrote: Hi Aom I think I managed to solve that problem by fixing my other problem with the troublesome UserForm. Here is what Dave Peterson suggested. You basically export the userform elsewhere, then delete the userform from your troublesome workbook, then import the exported userform back to the workbook. But I don't know if that'll fix the problem in your case: "Dave Peterson" wrote: Another thing to try against a copy of the troublesome workbook. Start a new workbook (just a temporary workbook). Open a copy of that troublesome workbook. Go into the VBE. Show the project explorer (ctrl-r if it isn't visible) Drag the userform from the troublesome workbook project to the temporary workbook project. Does it work ok there? If yes, try deleting the old userform in the troublesome workbook project. Drag the userform from the temporary workbook project back to the original workbook project. Does it work ok there? If yes, save the copy and use that instead. "aom" wrote: I got a problem same with you.... did you solved it? if yes...please let me know how to do this thank you Aom "Sam Kuo" wrote: Hi Gary The user might or might not have a workbook open already before open my userform by clicking my menu. But in a typical situation, the user would start the Excel application, which by default opens with a new workbook, Book1, as normal (or they might have some workbooks they're working in open already). They may then click on my menu in the toolbar to open my userform. So I guess this fits what you said about "using a menu in one workbook to open another workbook"? I've just tried your suggestion to hold the Shift key while clicking on my menu with Book1 open. This opens my userform OK, but Book1 now appears above my workbook (i.e. Book1 becomes the current workbook) and whatever I do in my userform that has a reference to my workbook throws a run-time error "9: Subscript out of range". I guess I need a line to make my workbook current when my userform open to solve this problem? And if so, can you please show me how? I'm using 2003, but where would I find the run box you mentioned and what does it do? Thanks for your patient. Sam "Gary Keramidas" wrote: a couple of thoughts: are you using a menu in one workbook to open another workbook? if so, open the 2nd workbook manually and see if it works.. if the form is set to run on open: 1. hold the shift key while you open the 2nd workbook so excel opens in safe mode (or if you're running 3003 and installed in the default location, type this in the run box: "C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE" /s .. then look at your form code. if it's ok, export the form, delete it. open the workbook and import the form and see what happens. -- Gary "Sam Kuo" wrote in message ... Hi Sorry the story may seem long winded, but I'm trying to describe it logically so the problem can be easily understood. I have added a new menu to the toolbar at the top of Excel, and assigned a macro (called "USLE") to it that opens a userform. It was all working fine until I made some changes to my VBE code, and I don't know what I've done that causes the Excel to shut when I click on my menu... Here is what happens: When I click on my menu, the Security Warning window prompts and I click "Enable Macros" as usual, the workbook then opens (but NOT the userform when it should!), and an error message prompts saying "Microsoft Office Excel has encountered a problem and needs to close", with the "Recover my work and restart Microsoft Office Excel" option checked. If I click on the recovered file in the Document Recovery panel in the left of the new Excel opened automatically, save it and close the workbook (but keep the Excel open), then click on my menu again, the userform then opens OK and everything is fine! However, if I start a new Excel application and click on my menu, the same problem happens again... Why is this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I insert forced linebreaks in cells in Excel | Excel Discussion (Misc queries) | |||
Forced new line in a cell: Excel | Excel Discussion (Misc queries) | |||
Forced reboots -- Making Excel save always before file close | Excel Discussion (Misc queries) | |||
Marco Run on File Close | Excel Programming | |||
Forced recalculation in Excel | Excel Programming |