Possible Bug in Excel UserForm?

[Gene]

I have created an Excel multi-page userform to gather some data from end
users. The form is locking up Excel when a sequence of steps is followed. I
have decomposed this problem and can find no reason for the behavior. I need
an expert's assistance to determine if I need to report this as a bug or if I
am not coding this properly. I've provided detailed steps below to replicate
the problem.

Thanks very much in advance for your help,
Gene Loughran

A. Please download the Userform from my website:
http://www.genesyspc.com/files/UserForm_test_0.xls

B. It has been virus scanned, but by all means, it never hurts.

C. Use the following directions to replicate the problem:

1. Open the Excel UserForm File & Enable Macros

2. Start the UserForm by selecting "FormOpen" from the Macro list.

3. Click Next to go to the first "Case"

4. Click on the "Evaluate" button for the 2nd Alternative

5. Enter a number in Criteria 2 / Element 2

6. Click the "Return" button

7. Click on the "Save" button

8. Click "Yes" to exit the Userform

9. Exit Excel
--------------
10. Open the Excel UserForm File & Enable Macros

11. Start the UserForm by selecting "FormOpen" from the Macro list.

12. Click Next to go to the first "Case"

13. Click on the "Evaluate" button for the 1st Alternative

14. Enter a number in Criteria 1 / Element 1

15. Click on any other function (such as Criteria 1 / Element 2


** Excel should have locked up at this point. If it has not, repeat steps 6
- 15, replacing step 13 with step 13b, below:

<< 13b. Click on the "Evaluate" button for the 3rd Alternative >>

....continue with step 14 above.

================================================

 

[Peter T]

I had a quick look but didn't get very far, breaking with a errors trying to
refer to a range names that don't exist (eg cs4a1tracker & c4a1evalbtn).

Why not create the scenario you think will cause the lock up, then step
through with F8 starting in the "FormOpen" proc. Also put a break on all
control events and likewise step through.

Regards,
Peter T

 

[Gene]

Peter,

Thank you for writing! I wasn't able to duplicate the errors you
reported...and tried the form on 3 separate XP & Excel 2003 machines. I did
notice a problem in my "back" and "next" button, so I coded a fix to that and
uploaded a new copy to my server...but that did not affect the problem.

I have stepped through the code (a lot!) and the funny thing is that the
form works fine with the VBE open. No errors at all. That is currently the
workaround I am using with my survey takers.

Curious about the problems you experienced...so I tried it on another
machine...and got the errors you reported when I ran it from the browser.
Please right-click and save it to your computer before running. Once I did
that, it ran fine.


Thanks!

 

[Gene]

Responders: PLEASE right-click and save the file to your desktop before
running.

Thanks!

 

[NickHK]

Gene,
OK, I see the same behaviour, XL 2K.
I haven't checked your code yet, but you can see XL using 99% CPU, with
memory steadily rising, so check you loops that none are infinite.

You should add some error handling to your routines to make the tracing of
this/these bug more easy.

NickHK
P.S. I can generate Peter's error if I click Next before Return. If this
should not be possible, disable the Next button until is usable.

 

[Peter T]

Hi Gene,

I had downloaded and saved your xls to file. Perhaps I pressed buttons in
the wrong order, error was due to something like this

'in normal module
n = Userform1.myPublicVar ' 4
Set rng = Range("myName" & n)

but myName4 doesn't exist (I got various errors like that).

I have stepped through the code (a lot!) and the funny thing is that the
form works fine with the VBE open. No errors at all.

When things work with the VBE open but fail or crash with it closed can
indicate an object ref is not getting a chance to compile. An example is
adding a new sheet and trying to refer to it by its codename. I know you are
not doing that but I notice you start writing to public variables in the
form module and control properties before loading the form.

Normally the first time you refer to a form it will load and it's initialize
event will run. But try instead

Userform1.load
Userform1.myPublicVarA = 123
Userform1.show

or better still refer to all your userform module level variables using
Property Let/Get pairs.

Include error handling throughout.

If still problems occur write a log to a text file each step of the way
(search this ng for examples), then you can find where it breaks even with
the VBE closed.

Regards,
Peter T

 

[Peter T]

Typo -

Userform1.load

should of course be -

Load UserForm1

Peter T

 

[Gene]

Nick,

Thank you for checking it out! I saw the Next/Return error after Peter
pointed it out and posted an updated copy which should correct the problem.

You are describing the exact same behavior...and so I know Excel is being
caught in a loop somehow, but when I try to step through it in the VBE -- the
problem disappears. Also, when I run the program the first time (i.e., prior
to saving and reopening), the program works fine--even with the VBE closed.
However, if you re-open it, you should get the error again. Very puzzling.

You're right, the error handling is a good idea. I was trying to keep the
code as simple as possible, but I will try to add a good error trap there and
see what I find... maybe some time-out trap since it doesn't generate an halt
error?

 

[Gene]

Many thanks Peter! I will run through your solution and write back.