UserForm1.show fails if another workbook is open
 

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

UserForm1.show fails if another workbook is open
 

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


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=555233

UserForm1.show fails if another workbook is open
 

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

"Leith Ross" wrote:


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


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=555233

UserForm1.show fails if another workbook is open
 

ello Gary,

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

Dim MyWorkbook As Workbook
Set MyWorkbook = ThisWorkbook

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=555233