Relating Worksheet(I).Name with VBComponent(J).Name
 

In the VBE, each Microsoft Excel Object has a VBComponent name and a
Worksheet name. Programatically, I can interrogate either the
Worksheets or the corresponding VBComponents. How do I tie the two
together?

Given a VBComponent (that is associated with a worksheet), how do I
determine its associated worksheet name or index?

Given a worksheet, how do I determine its associated VBComponent name
or index?

Thanks


---
Message posted from http://www.ExcelForum.com/

Relating Worksheet(I).Name with VBComponent(J).Name
 

MWE,

Given a VBComponent, you have to loop through the worksheets to
find the worksheet whose code name is the same as the VBComponent
name. E.g,

Function VBCompToWSName(VBComp As VBIDE.VBComponent) As String
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
If WS.CodeName = VBComp.Name Then
VBCompToWSName = WS.Name
Exit Function
End If
Next WS
End Function

Given a Worksheet, you can get its CodeName property to find the
associated VBComponent. For example,

Function WSToVBComp(WS As Worksheet) As VBIDE.VBComponent
Set WSToVBComp =
WS.Parent.VBProject.VBComponents(WS.CodeName)
End Function


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"MWE " wrote in message
...
In the VBE, each Microsoft Excel Object has a VBComponent name
and a
Worksheet name. Programatically, I can interrogate either the
Worksheets or the corresponding VBComponents. How do I tie the
two
together?

Given a VBComponent (that is associated with a worksheet), how
do I
determine its associated worksheet name or index?

Given a worksheet, how do I determine its associated
VBComponent name
or index?

Thanks


---
Message posted from http://www.ExcelForum.com/