Run-time error '9'

[Scott]

I encountered "Run-time error '9': Subscript out of range" at this line:

Form_MyForm.Show

The form is sitting there and no spelling error. Anyone can help?

Thanks.

 

[Bob Umlas]

Something inside the initialize event is causing the error. Put a breakpoint
in the initialize code and step through to see where it fails.
Bob Umlas
Excel MVP

 

[Rubble]

Hey Scott -

Is the name of your form "Form_MyForm" ? That is the syntax I use to pull
up my forms - so I would assume that it relates to something you put on the
form. Did you load data onto your form prior to calling Form_MyForm.Show?

 

[Dan]

Is the name Form_MyForm or just MyForm? In the later case, you should use
MyForm.Show. I would also use MyForm.Show (vbModeless) or MyForm.Show
(vbModal), depending on what you want the form to do.

Dan

 

[Scott]

Thank you all for the replies.

Here are the codes:
Private Sub CommandButton1_Click()
Form_Main.Hide
Form_MyForm1.Show
End Sub

The program worked fine yesterday. I made some changes today and I cannot
remember every step I did. Suddently, I got into this trouble. Strangely,
when I added a new form "UserForm1" and replaced the "Form_MyForm1.Show" with
"UserForm1.Show" above, it just worked.

I guess I probably broke a link with "Form_MyForm1.Show", but have no clue
how I did and what to do to correct it.

 

[Chip Pearson]

By default, error trapping is set at "break unhandled errors". A side
effect of this is that any error that occurs in an object module
(e.g., a class module or userform) will cause the code to stop and
enter debug mode on the line of code that created an instance of an
object.

In your code, there is really nothing wrong with the code
Form_MyForm.Show, and you could spend all day trying to figure out
what is wrong. However, this is the code that instantiates the form
to an object. Any error in the form will cause a break on this line of
code.

In VBA, go to the Tools menu, choose Options, then the General tab.
There, ensure that "Break In Class Module" is selected. When this
option is in effect, the compiler will break on the actual line of
code that is causing the problem. You should ALWAYS have Break In
Class Module in effect. I can't think of any good reason that you
would want it otherwise.

An error 9 -- subscript out of range -- typically occurs when you
attempt to retrieve an item from a collection and that item doesn't
exist. For example, if you have no worksheet named "DoesNotExist", the
code Set WS = Worksheets("DoesNotExist") will throw an error 9 because
there is no such sheet in the Worksheets collection.


Cordially,
Chip Pearson
Microsoft MVP
Excel 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Scott]

Problem solved. Thank you very much.

By default, error trapping is set at "break unhandled errors". A side
effect of this is that any error that occurs in an object module
(e.g., a class module or userform) will cause the code to stop and
enter debug mode on the line of code that created an instance of an
object.

In your code, there is really nothing wrong with the code
Form_MyForm.Show, and you could spend all day trying to figure out
what is wrong. However, this is the code that instantiates the form
to an object. Any error in the form will cause a break on this line of
code.

In VBA, go to the Tools menu, choose Options, then the General tab.
There, ensure that "Break In Class Module" is selected. When this
option is in effect, the compiler will break on the actual line of
code that is causing the problem. You should ALWAYS have Break In
Class Module in effect. I can't think of any good reason that you
would want it otherwise.

An error 9 -- subscript out of range -- typically occurs when you
attempt to retrieve an item from a collection and that item doesn't
exist. For example, if you have no worksheet named "DoesNotExist", the
code Set WS = Worksheets("DoesNotExist") will throw an error 9 because
there is no such sheet in the Worksheets collection.


Cordially,
Chip Pearson
Microsoft MVP
Excel 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

I encountered "Run-time error '9': Subscript out of range" at this line:

Form_MyForm.Show

The form is sitting there and no spelling error. Anyone can help?

Thanks.

.