Referring to CodeModule Sheet Name

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tcb - 20 Dec 2007 19:42 GMT

When referring to:

With XLDestWB.VBProject.VBComponents("Sheet1").CodeModule

is it possible to refer to the code by Sheet1's tab name (e.g.
"TabOne") rather than "Sheet1"?

If not, how can I determine which Sheet number TabOne is (in VBA)?

Reply to this Message

Dave Peterson - 20 Dec 2007 19:54 GMT

With XLDestWB.VBProject.VBComponents(worksheets("tabone").codename).CodeModule

is one way.

> When referring to:
>
[quoted text clipped - 4 lines]
>
> If not, how can I determine which Sheet number TabOne is (in VBA)?

Signature

Dave Peterson

Reply to this Message

Dave Peterson - 20 Dec 2007 19:59 GMT

And it would be better to qualify that worksheets reference, too:

With XLDestWB.VBProject.VBComponents(xldestwb.worksheets("tabone").codename) _
.CodeModule

or
with xldestwb
With .VBProject.VBComponents(.worksheets("tabone").codename).CodeModule
...
end with
end with

> With XLDestWB.VBProject.VBComponents(worksheets("tabone").codename).CodeModule
>
[quoted text clipped - 12 lines]
>
> Dave Peterson

Signature

Dave Peterson

Reply to this Message