Create Excel sheet with worksheet_change code-CodePudding

I have code that creates a worksheet and the worksheet contains a table. I then programmatically add an advanced filter to the table in the created worksheet. This works fine, but the advanced filter does not refresh on change of the worksheet: A known issue.

The standard solution is to put code behind the worksheet_change event so the filter is refreshed when the worksheet changes. This also works fine when I add this code manually.

But when I create a sheet I want to automatically create the code for the new sheets worksheet_change event. I don't know how to programmatically specify code for my new sheet in the worksheet_change event.

Is this possible? Or is there another way to accomplish this?

CodePudding user response：

Maybe look at using this instead. It'll fire for all sheet changes and allows your code to work centrally ...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
End Sub

CodePudding user response：

You can create a class module with the event and hook that to your worksheets. So you can add a hidden sheet with all workbook names that you want to add that event to.

So if you programmatically add a new sheet you just need to add its name to that list.

So first add a sheet named MyClassSheets (you can make it hidden so no user sees it). And add some sheet names there:

So this means we want to run your worksheet_change event in Sheet2 and Sheet3 only.

Then we add a class module MyWsClass:

Option Explicit

Public WithEvents Ws As Worksheet

Private Sub Ws_Change(ByVal Target As Range)
    MsgBox Target.Address(False, False) & " changed."
End Sub

This is the event we want to run. It just shows a message box which cell was changed. You need to adjust that to your wishes.

Finally we need to hook those events to the worksheets. So we add a normal module (non-class module) HookWsEvents:

Option Explicit

Dim MyWorksheets() As New MyWsClass

Public Sub HookEvents()
    ' get list of worksheets we want to add the class
    With ThisWorkbook.Worksheets("MyClassSheets")
        Dim MyClassSheets() As Variant
        MyClassSheets = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).Value
    End With
    
    ReDim MyWorksheets(UBound(MyClassSheets) - 1)
    
    Dim i As Long
    ' add class to those worksheets
    Dim Ws As Variant
    For Each Ws In MyClassSheets
        Set MyWorksheets(i).Ws = ThisWorkbook.Worksheets(CStr(Ws))
        i = i   1
    Next Ws
End Sub

So now we just need to make sure that the procedure HookEvents is called everytime the workbook opens. So we add the following into ThisWorkbook:

Option Explicit

Private Sub Workbook_Open()
    HookEvents
End Sub

After running HookEvents the event runs in every sheet that is listed in MyClassSheets.

If you now add a new worksheet that you want to add the event to, you just need to add it to the list in MyClassSheets and run HookEvents again. Therefore we can add a small helper procedure to our HookWsEvents module:

Public Sub AddWsToList(Ws As Worksheet)
    With ThisWorkbook.Worksheets("MyClassSheets")
        ' add the worksheet name of Ws to the list in MyClassSheets
        .Cells(.Rows.Count, "A").End(xlUp).Offset(RowOffset:=1).Value = Ws.Name
    End With
End Sub

So now we can use the following code

' add a new worksheet as last worksheet
Dim NewWs As Worksheet
Set NewWs = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))

' give it a name
NewWs.Name = "Sheet5"

' add it to the list of MyClassSheets
AddWsToList NewWs

' hook events to all sheets
HookEvents

to add a new worksheet and add the event to it.

You may want to add some error checking if a worksheet in the list of MyClassSheets does not exist anymore (was deleted) so your code does not thorw an unhandled exception in this case. I left this out to have the above explanation more clear.

If all your sheets have someting common in their name you don't need to maintain that MyClassSheets list. So for example if all the sheets you want to add the event to start with XYZ_ you just need to loop over all sheets in the HookEvents procedure and check if they start with XYZ_ to add them to the MyWorksheets array then. Resizing that array may be a bit more complicated then.