Run time error on Userform Show

[Guest]

I have a simple app - one userform that I call from a command button on the
spreadsheet.
A couple of times - I run into an error message when trying to show the
userform. Either a subscript out of range (I am not using subscripts) or a
type mismatch error.
I try renaming the form - no good. I try loading it then showing it - still
no good.

I got the following off of the website here - I named the form UserForm1
(very original I admit) - as soon as it gets to the load UserForm1 I get the
subscript error. I have used other names - renamed the form - nothing works.

I have run into this a couple of times before - Excel gets into a funk and
will not recognize the form. The funny thing is that the code will work
during the early stages of development - then I add a few more items to the
form and it gets into this run time error thing.

The MS website info addresses collections of userforms and getting this
error - but I have only a single form I am trying to display.

Any help is greatly appreciated - I tried all 3 of the "workarounds" as
suggested on the MS website and none of them worked.

 

[Nick Hodge]

Don

Error 9 'Subscript out of range' means that you are referring to an item in
a collection (Your one form in the forms collection) that does not exist.
So for example, if you had line

UserForm1.Show

where there was no form called UserForm1 you will get this error

Check your code is referring to the same 'name' as in the (Name) setting in
the properties dialog. The caption (Name at the top of the form is
irrelevant in this case)

One your worksheet button, make sure the TakeFocusOnClick property is set to
false and check the types you have declared any variables at, as a type
mismatch is that something is returning a value (type) which is not expected
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Tim Zych]

I would first rule out something on the form that might be botched during
the load event. You say the form works for a while and then you add more
items and it happens....so it sounds like it's one of the controls or the
data getting loaded into them. If you remove all of the controls and all of
the code behind, then load the form, does it load?

To narrow down the problem, remove each control one-by-one and try to load
the form.