Home |
Search |
Today's Posts |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subscript out of Range Error | Excel Programming | |||
Runtime Error - Subscript out of range despite On Error statement | Excel Programming | |||
Subscript out of range error - save copy error | Excel Programming | |||
Subscript out of range error - save copy error | Excel Programming | |||
Type Mismatch error & subscript out of range error | Excel Programming |