| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to a Worksheet with a variable
This The expression works OK:-
Set VBComp = ThisWorkbook.VBProject.VBComponents("Sheet1") But what is the correct syntax if I want to refer to a sheet with a variable number? Suppose the variable sheet number is MySh, then ("SheetMySh") produces a runtime error "Script out of range" -- donwb |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Referring to a Worksheet with a variable
MsgBox Application.Worksheets(1).Name
MsgBox Application.Sheets(1).Name Substitute your variable for 1 -- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "donbowyer" wrote: | This The expression works OK:- | Set VBComp = ThisWorkbook.VBProject.VBComponents("Sheet1") | But what is the correct syntax if I want to refer to a sheet with a variable | number? | Suppose the variable sheet number is MySh, then ("SheetMySh") produces a | runtime error "Script out of range" | -- | donwb |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Referring to a Worksheet with a variable
Sheets("MySheet")
"donbowyer" wrote: This The expression works OK:- Set VBComp = ThisWorkbook.VBProject.VBComponents("Sheet1") But what is the correct syntax if I want to refer to a sheet with a variable number? Suppose the variable sheet number is MySh, then ("SheetMySh") produces a runtime error "Script out of range" -- donwb |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Referring to a Worksheet with a variable
Set VBComp = ThisWorkbook.VBProject.VBComponents(MySh)
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "donbowyer" wrote in message ... This The expression works OK:- Set VBComp = ThisWorkbook.VBProject.VBComponents("Sheet1") But what is the correct syntax if I want to refer to a sheet with a variable number? Suppose the variable sheet number is MySh, then ("SheetMySh") produces a runtime error "Script out of range" -- donwb |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Referring to a Worksheet with a variable
Thanks for the inputs but still no go.
Perhaps if I repeat the problem. The expression:- Set VBComp = ThisWorkbook.VBProject.VBComponents("Sheet1") works fine, but Set VBComp = ThisWorkbook.VBProject.VBComponents("SheetMySh") where "MySh" is a variable sheet number replacing the "1" Does not work. I think it's in the syntax, but not sure. -- donwb "Dave Patrick" wrote: MsgBox Application.Worksheets(1).Name MsgBox Application.Sheets(1).Name Substitute your variable for 1 -- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "donbowyer" wrote: | This The expression works OK:- | Set VBComp = ThisWorkbook.VBProject.VBComponents("Sheet1") | But what is the correct syntax if I want to refer to a sheet with a variable | number? | Suppose the variable sheet number is MySh, then ("SheetMySh") produces a | runtime error "Script out of range" | -- | donwb |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Referring to a Worksheet with a variable
Thanks Bob - simple when you know how: that works fine
-- donwb "Bob Phillips" wrote: Set VBComp = ThisWorkbook.VBProject.VBComponents(MySh) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "donbowyer" wrote in message ... This The expression works OK:- Set VBComp = ThisWorkbook.VBProject.VBComponents("Sheet1") But what is the correct syntax if I want to refer to a sheet with a variable number? Suppose the variable sheet number is MySh, then ("SheetMySh") produces a runtime error "Script out of range" -- donwb |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Referring to a Worksheet with a variable
Try;
Set VBComp = ThisWorkbook.VBProject.VBComponents("Sheet" & MySh) MsgBox VBComp.Name -- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "donbowyer" wrote: | Thanks for the inputs but still no go. | Perhaps if I repeat the problem. | The expression:- | Set VBComp = ThisWorkbook.VBProject.VBComponents("Sheet1") | works fine, but | Set VBComp = ThisWorkbook.VBProject.VBComponents("SheetMySh") | where "MySh" is a variable sheet number replacing the "1" | Does not work. | I think it's in the syntax, but not sure. | -- | donwb |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Referring to a Worksheet with a variable
Doesn't that return ThisWorkbook ? Or maybe that's what you wanted.
-- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "donbowyer" wrote: | Thanks Bob - simple when you know how: that works fine | -- | donwb |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Referring to a variable cell on another sheet | Excel Worksheet Functions | |||
| Referring to a variable file name | Excel Discussion (Misc queries) | |||
| Recall a variable referring to a worksheet name? | Excel Programming | |||
| Referring to a worksheet using variable | Excel Programming | |||
| Referring to a local or global variable or constant dynamically? | Excel Programming | |||
Linear Mode
