Register Event for automatically generated Worksheet in Excel

Question

When the user doubleclick on a cell in a Pivot-Table, Excel generates a new Worksheet with the Details. That is perfect. I am searching for a possibility to register a VBA-Event in this new Worksheet. I want register a function for the Event Worksheet_SelectionChange for this new Worksheet. How can I do this?

Answer 1

It is a little bit complicated, but with your Reputation, I'm sure you don't fear complications :)

First of all in your Excel File, make sure you Reference VBIDE in your reference using the following:

1. You need the VBIDE Reference for this to Work:

1.a. You can run the following Code in your Immediate Window to attach the Reference:

ThisWorkbook.VBProject.References.AddFromGuid _
        GUID:="{0002E157-0000-0000-C000-000000000046}", _
        Major:=5, Minor:=3

1.b. Or you can attach manually "Microsoft Visual Basic for Applications Extensibility 5.3" from your Tools\References

2. Once the Reference is Attached, you can add the following function into your WorkBook:

Const DBLQuote = """"

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim Line As String

        With ThisWorkbook.VBProject.VBComponents(Sh.Name).CodeModule
            Line = .CreateEventProc("SelectionChange", "Worksheet") + 1
            .InsertLines Line, _
            "Msgbox " & DBLQuote & "Selection Changed !" & DBLQuote & ",vbOkOnly"

            '----> You can put your code here or call your function 
        End With
End Sub

For getting this, I had to dig deeply but thank God it works. For more information, you can go here:

http://www.cpearson.com/Excel/vbe.aspx for the VBE

I hope this will help you!