Identifiying a Worksheets VBComponent

[Guest]

I am trying to use a macro to add a Selection Change event to a sheet created
in another workbook. The problem that I am having is identifying the
VBComponent for the sheet in question.

I have two workbooks, TargetBook which initially has just one worksheet, and
MacroBook which contains a macro that adds sheets to Targetbook and adds the
Selection Change event to the first new sheet added to TargetBook. When this
macro is run the first sheet added to TargetBook is identifed as Sheet2 in
the VBE and I can define my VBComponent as:

Set VBComp = Workbooks("TargetBook.xls").VBProject.VBComponents("Sheet2")

However if the user deletes the newsheets and reruns the macro (which they
may want to do) the sheet which should have the event procedure is no longer
"Sheet2" in the project.

Knowing the sheets Excel name and index, how do I identify what the VBE
component name is for the sheet?

Many thanks
Rowan

PS I have looked through Chip Pearson's webpage on the subject but it didn't
answer this question.

 

[Dave Peterson]

Maybe something like:

Dim VBComp As Object 'CodeModule
Dim wks As Worksheet
With Workbooks("targetbook.xls")
Set wks = .Worksheets.Add
Set VBComp = .VBProject.VBComponents(wks.CodeName).CodeModule
End With

 

[Guest]

Thanks Dave

I missed the CodeName property.

Regards
Rowan