How to create a dynamic event procedure?

[alanw64]

Ok, I might be making this harder than it seems, but after multiple searches here and elsewhere, I can't seem to find the answer.

I have a macro that will add multiple worksheets. When the worksheet is added, is there a way to dynamically create code inside the Worksheet_Activate event procedure? The macro dialogbox will remain open (non-modal form), and ideally, if the user switches to another worksheet, I would like the macro dialogbox to know about it via the Worksheet_Activate event. Is that possible?

Basically, I would like my macro to create the following code for every worksheet that gets added:

Private Sub Worksheet_Activate()
Call MyMacroEvent
End Sub

I started reading about the VBA extensibility and the VBE, but I'm not sure if that's what I need or not. Hopefully there's an easier solution!

Thank you for any input,
Alan

 

[Jon von der Heyden]

Hi Alan

Are you familiar with the Workbook_SheetActivate(ByVal Sh As Object) event? It will trigger when ever you activate any sheet (In ThisWorkbook module). Would this be viable?

 

[EducatedFool]

You can also replace

    Worksheets.Add

with

    Worksheets("HiddenSheet").Visible = True
    Worksheets("HiddenSheet").Copy Worksheets("HiddenSheet")
    Worksheets("HiddenSheet").Visible = 2 ' hide

where HiddenSheet is a worksheet that contains some event procedures.

 

[Jon von der Heyden]

Good idea EducatedFool

 

[VoG]

Or you could use a template - example http://www.mrexcel.com/archive/VBA/14197.html

 

[alanw64]

...Workbook_SheetActivate(ByVal Sh As Object) event

Hi Jon,

No, I'm not familiar with this, but I think this is exactly what I need.

Thank you very much for your help, I really appreciate it!

 

[alanw64]

EducatedFool and VoG --

Excellent suggestions, too -- thank you very much!

"...ask and ye shall receive...!"