Excel is forced to close when run a marco???

[Sam Kuo]

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?

 

[JLGWhiz]

Very difficult to tell without being able to analyze the code that throws the
error.

 

[Gary Keramidas]

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.

 

[Sam Kuo]

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

 

[aom]

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]

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:

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]

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]

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

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

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: