VBComponents question

[Frederick Chow]

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.

[Tom Ogilvy]

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)

--
Regards,
Tom Ogilvy

"Frederick Chow" wrote:

> 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.
>
>
>

[Frederick Chow]

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> 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)
>
> --
> Regards,
> Tom Ogilvy
>
> "Frederick Chow" wrote:
>
>> 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.
>>
>>
>>