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)

 

[Frederick Chow]

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)