UserForm1.show fails if another workbook is open

G

Guest

If I have two excel files (both with VBA applications) open and I am running
VBA application from one of them, when I try to do “User1.showâ€, it fails. I
get a “Run Time error 9. Subscript out of range.â€

However if I close the application I am not using at the moment the
“User1.Show “ works as expected.

Any help would be appreciated,

Gary
 
L

Leith Ross

Hello Gary,

When 2 or more Workbooks are open and running VBA it is important to
use fully qualified object references. You can have 2 UserForm1 forms
declared. You have to tell Excel which form you want by qualifying the
reference with the workbook it belongs to.

Example:
Workbooks("Book1").UserForm1.Show

Sincerely,
Leith Ross
 
G

Guest

Leith,

Thanks for your response. It makes sense, but when I tried:

myworkbook = ThisWorkbook.Name ' shows current workbook in debugger

Workbook(myworkbook).UserForm1.show ' compile error !

I got "sub or Function not defiend error"

Any ideas ?

Gary
 
L

Leith Ross

ello Gary,

You need to use the SET statement to define your object reference...

Dim MyWorkbook As Workbook
Set MyWorkbook = ThisWorkbook

Sincerely,
Leith Ross
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top