userform and error excel has stopped working

[hekske]

hi,

i have created a userform in excel. when i debug it it works fine. when i
click on the button "Save" (does a save as) and rename the file until here no
problem. but when i try to open the newly saved file it gives an error like
"excel has stopped working ....". when i place a messagebox just before the
form.show command, it displays the messagebox and when the command form.show
needs to be executed the error appears again and i can close excel. if i open
de file without executing the code and change something (for exemple add an
empty line) in the code and save it, the file works again. like this i can
not send this userform to be used by our offices.
what could be the problem????? has anyone got an idea?

 

[Joel]

You need to put a break point at the SUB line in the userform and then step
through the code. You probably have to put a break point at the start of
each of the SUB's in the userform to find the problem.

 

[Joel]

Two more things. Change the Stop on Error mode in VBA tools - Options -
General - Error Stoppping. stop onj all errors. Also comment out all the ON
Error statements so you can find the source of the problem.

 

[broro183]

hi,

Can you please provide more details (eg Excel version, size of file,
ideally a copy of the file or at least the problematic section of
code)?
Does the file have lots of formulae or Charts?
What is "Form" declared as, does it work if you rename it to a more
unique word eg "UserInputForm"?

If you have made lots of code changes within the VBE during development
I suggest downloading & running 'Rob Bovey's Code Cleaner.'
(http://www.appspro.com/Utilities/CodeCleaner.htm)

hth
Rob

 

[hekske]

the version of excel is 2007 (12.06504.5001 sp2), the file size is 575 kb
there are no charts, formulas in the file. only some sheets (which contain
only text) and a userform (+ code).
how can i attach the file?

 

[hekske]

i put the error option to all errors but it stays the same. when i try to
debug, the error doesn't show. it only happens when i do a save (of the data
) and open the (new) file again (so that the code can be executed).

 

[broro183]

hi,

Unfortunately, I don't have access to Excel 2007 so I may not be able
to help even if you can upload it
I'm viewing the thread as a member of TheCodeCage (via this 'URL'
(http://tinyurl.com/nhtmq5)) which provides the ability to attach files.
You could become a member & upload a file to the thread then or Joel may
be able to resolve it for you without having a file in front of him...

It may be easier for you just to post the code via your normal
forum/community (rather than becoming a CodeCage member).

Also, have you tried the Code Cleaner addin?

Rob

 

[hekske]

hi,

the code cleaner didn't solve the problem. i can not post the code because
there is a lot of code(modules, subs, ... ) behind the userform.

Cindy

 

[broro183]

hi Cindy,

You need to put a break point at the SUB line in the userform and then
step through the code. You probably have to put a break point at the
start of each of the SUB's in the userform to find the problem.

To expand on Joel's suggestion, a break point can be inserted by
clicking a line of code near the start of each sub & pressing [F9] (note
that they can't be on declarations). From memory, break points only
exist for the life of the file being open, so in your case, it is likely
to be better if you type the word Stop on a line by itself near the
start of each sub. The "stop" phrase has the same action (as a break
point) but it will still be there when you re-open the file & will cause
the code to be interrupted when it is reached. The stop phrase will
highlight & you can then use [F8] to manually continue the code line by
line or [F5] to automatically continue. You can then narrow down which
macros work & where the error occurs by noting down each macro as it
runs. To see the hierarchy of called macros at any time when the code is
interrupted you can press [ctrl + L] to bring up the Call Stack.
Note: when you fix the problem you should comment out (or delete) all
the "stop" lines within your add-in.

I'm sorry no other solutions spring to mind, but I've realised that I
may be able to help if you were to become a CodeCage member (it's free &
you can list me "broro183" as your referrer via 'The Code Cage - Help
Forums For Microsoft Office Tips & Tricks'
(http://www.thecodecage.com/register.html)). Then you can upload a
zipped copy of the file in Excel 2007's compatibility mode of xls format
for Excel '97 to 2003 into this thread (http://tinyurl.com/nhtmq5).

hth
Rob