Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifiying a Worksheets VBComponent
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifiying a Worksheets VBComponent
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 Rowan wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifiying a Worksheets VBComponent
Thanks Dave
I missed the CodeName property. Regards Rowan "Dave Peterson" wrote: 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 Rowan wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBIDE.VBComponent | Excel Discussion (Misc queries) | |||
VBComponent Types | Excel Programming | |||
Compile Error on "dim x as VBComponent"? | Excel Programming | |||
Relating Worksheet(I).Name with VBComponent(J).Name | Excel Programming | |||
problem with wkbk.VBProject.VBComponent | Excel Programming |