Hi Simon,
Is this a sub in a Useform module or code that is called from a sub in a
userform module? I have often had strange problems with code error handling
with subs either in or called from Userform modules. Some problems
experienced are.
Total freeze of Excel.
Ignores the code and just doesn't run.
Error messages that don't match the real error.
Firstly, are you compiling the code before attempting to run it? If not try
menu item Debug -> Compile. Sometimes this will highlight code errors that
cause problems like you are having.
Next:
What I have sometimes had to do is remove the code from the Userform module
and place it in a standard module. Replace all references to Me. with the
Userform's name. (Or, if Me. was not used because mostly it is not required
when in the Userform's module, insert the userforms name for all userform
controls etc .)
Attempt to compile the code and fix any compile errors.
When you show the form, show it as modeless so that you can get back to the
VBA editor to run the code directly from the standard module without a call
from a from a form control. Then run the code from the editor and it will
usually stop on the real errors. Once the real errors are fixed, it can
usually be put back into the Userform's module.
However, having said to show the form as modeless, you can't do that if you
are using RefEdit controls (controls where you can select a worksheet range)
on the form because they do not work in Modeless forms. If you do not enter
the ?RefEdit control no problem but if you do then Excel freezes.
If the above does not work or the code is already in a standard module, try
making a copy of the workbook and remove the code that you say is working and
just leave the failing code and see if it will compile run.
