VBComponents question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Frederick Chow - 20 Mar 2006 17:46 GMT

Hi all,

I need to programmatically add codes to a sheet module which has been
renamed previously.

Let's say such module's internal name is Sheet8, but the sheet has been
renamed as, say "ABCDE".

Now I found that I could not properly refers to that sheet's module by
writing VBProject.VBComponents("ABCDE"), but
VBProject.VBComponents("Sheet8") will be fine.

My question is, is it ever possible to refer to that VB Component with
"ABCDE", and not "Sheet8"? This is necessary because the original sheet name
("Sheetx") differs each time when I need to add code to it. Thanks for your
assistance.

Frederick Chow
Hong Kong.

Reply to this Message

Tom Ogilvy - 20 Mar 2006 19:25 GMT

not directly.

you can do

For Each vbc In ThisWorkbook.VBProject.vbcomponents
If vbc.Type = vbext_ct_Document Then
If vbc.Name <> "ThisWorkbook" Then
If vbc.Properties("Name") = "ABCDE" Then
s = vbc.Properties("_codename").Value
Set vbc1 = vbc
Exit For
End If
End If
End If
Next
MsgBox s & ", " & vbc.Name
End Sub

so vbc1 will be a reference to the component (or you have the name in s or
you could get it with vbc1.name)

Signature

Regards,
Tom Ogilvy

> Hi all,
>
[quoted text clipped - 15 lines]
> Frederick Chow
> Hong Kong.

Reply to this Message

Frederick Chow - 20 Mar 2006 20:18 GMT

> not directly.
>
[quoted text clipped - 38 lines]
>> Frederick Chow
>> Hong Kong.

Reply to this Message