UserForms -- Subscript out of range error
 

Maybe an off the wall suggestion, but how about fixing the Subscript error?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Matt Nale" <Matt wrote in message
...
I have an an Excel application that runs entirely through UserForms (Excel
is
not visible at all). These forms are not shown modally, so users can open
other workbooks to get numbers, etc from. When a user goes to another
workbook, my application's workbook deactivates before the new one can
activate. With this new workbook active the user tries to enter a value
into
my UserForm and gets an error, "Subscript out of range". Since my
workbook
is not visible it is impossible for the user to click on my workbook to
activate it again.

One solution I have come up with is writing a procedure that activates the
workbook that was active when the application opened and then applying
that
procedure to every event or in the error event if the "Subscript..." error
is
fired. This just seems inefficient.

Another solution I have thought of is keeping my UserForms modal and
adding
a "Pause" (or something to that effect) button. Then the user can go
about
looking at other information and then when they come back to my UserForm
they
can hit that button again and that will activate my workbook. I do not
really want to do this because of spatial reasons and a loss of
functionality.

Is there any way that you can lock a UserForm to only use a certain
workbook
no matter which workbook is active? Or any other possibilities?

Thanks in advance.

UserForms -- Subscript out of range error
 

If that is the case, I think you should definitely track it down. It may be
a real problem without knowing it.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Matt Nale" wrote in message
...
I can only assume I am getting the subscript error because the application
is
trying to reference something in my workbook and not finding it because
another workbook is active.

I think I have found a solution...

In the Workbook_Open event for my application I get the ActiveWorkbook and
put it in a public variable, WbName.

Then whenever I reference my a worksheet I use this notation:
Workbooks("WbName").Sheets("ShName").Range("RngNam e")

"Bob Phillips" wrote:

Maybe an off the wall suggestion, but how about fixing the Subscript
error?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Matt Nale" <Matt wrote in message
...
I have an an Excel application that runs entirely through UserForms
(Excel
is
not visible at all). These forms are not shown modally, so users can
open
other workbooks to get numbers, etc from. When a user goes to another
workbook, my application's workbook deactivates before the new one can
activate. With this new workbook active the user tries to enter a
value
into
my UserForm and gets an error, "Subscript out of range". Since my
workbook
is not visible it is impossible for the user to click on my workbook to
activate it again.

One solution I have come up with is writing a procedure that activates
the
workbook that was active when the application opened and then applying
that
procedure to every event or in the error event if the "Subscript..."
error
is
fired. This just seems inefficient.

Another solution I have thought of is keeping my UserForms modal and
adding
a "Pause" (or something to that effect) button. Then the user can go
about
looking at other information and then when they come back to my
UserForm
they
can hit that button again and that will activate my workbook. I do not
really want to do this because of spatial reasons and a loss of
functionality.

Is there any way that you can lock a UserForm to only use a certain
workbook
no matter which workbook is active? Or any other possibilities?

Thanks in advance.

UserForms -- Subscript out of range error
 

Yu could also set a public worksheet variable

Set oSheet = ThisWorkbook.WorkSheets("ShName")

and then in the code use

oSheet.Range("RngName")


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Matt Nale" wrote in message
...
I can only assume I am getting the subscript error because the application
is
trying to reference something in my workbook and not finding it because
another workbook is active.

I think I have found a solution...

In the Workbook_Open event for my application I get the ActiveWorkbook and
put it in a public variable, WbName.

Then whenever I reference my a worksheet I use this notation:
Workbooks("WbName").Sheets("ShName").Range("RngNam e")

"Bob Phillips" wrote:

Maybe an off the wall suggestion, but how about fixing the Subscript
error?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Matt Nale" <Matt wrote in message
...
I have an an Excel application that runs entirely through UserForms
(Excel
is
not visible at all). These forms are not shown modally, so users can
open
other workbooks to get numbers, etc from. When a user goes to another
workbook, my application's workbook deactivates before the new one can
activate. With this new workbook active the user tries to enter a
value
into
my UserForm and gets an error, "Subscript out of range". Since my
workbook
is not visible it is impossible for the user to click on my workbook to
activate it again.

One solution I have come up with is writing a procedure that activates
the
workbook that was active when the application opened and then applying
that
procedure to every event or in the error event if the "Subscript..."
error
is
fired. This just seems inefficient.

Another solution I have thought of is keeping my UserForms modal and
adding
a "Pause" (or something to that effect) button. Then the user can go
about
looking at other information and then when they come back to my
UserForm
they
can hit that button again and that will activate my workbook. I do not
really want to do this because of spatial reasons and a loss of
functionality.

Is there any way that you can lock a UserForm to only use a certain
workbook
no matter which workbook is active? Or any other possibilities?

Thanks in advance.