Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBComponents question
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBComponents question
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Thanks a lot (No Content)
"Tom Ogilvy" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changeing codename of worksheet via VBComponents collection | Excel Programming | |||
?thisworkbook.VBComponents(5).Type returns 2 | Excel Programming | |||
Links between VBComponents and Sheets 3 | Excel Programming | |||
Links between VBComponents and Sheets 2 | Excel Programming | |||
Links between VBcomponents and Sheets | Excel Programming |