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

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

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

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

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

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

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

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