CODEWITHSUNDEEP
vbaexcel
skatun
skatun

Reputation: 877

Detect the event ActiveWorkbook.Save from personal.xlsm in excel

I have all macros stored in personal.xlsm and untill now I have used a standard filter to hide/show columns. The new feature I want to implement now is that each user can have their own filter if they would like to. So basically i look in a folder for a personal filter if it exist and if it does it use that filter instead of the standard one.

But my problem is that i want to load a personal filter on workbookOpen event and reset to standard filter on the beforeClose event. My question is if I can do this from personal.xlsm in a way? Or do I have to manually go through all 250 workbooks and add in thisworkbook module onOpen and beforeClose events to call my method createFilter and resetFilter?

Here is a link to personal.xlsm for those who are not familiar with that methodology

Upvotes: 0

Views: 214

Answers (2)

skatun
skatun

Reputation: 877

Here is the code in helper if someones needs it:

Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
    Dim myPath As String
    On error resume next
    If InStr(UCase(ActiveWorkbook.Name), "PARTSLIST") And (InStr(UCase(ActiveWorkbook.Name), "FILTERPARTSLIST") = 0) Then
        myPath = Left(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\") - 1)
        If Dir(myPath & "\filterPartsList.xlsx") <> "" Then  '
            Call ColumnCreater.updateFilter
        Else
            Call ColumnCreater.filterCreationStandard
        End If
    End If
End Sub

Upvotes: 0

Excel Developers
Excel Developers

Reputation: 2825

In personal.xlsm, create a class module named "Helper". Place the following code in it:

Option Explicit

Public WithEvents xlApp As Application

Private Sub Class_Initialize()

    Set xlApp = Application

End Sub

Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)

    'Your code here

End Sub

Your code for loading a filter should go in the 'Your code here bit.

Add a standard code module and add this code:

Public MyHelper As Helper

Finally, in the Workbook_Open event of personal.xlsm, place this code:

Private Sub Workbook_Open()

    Set MyHelper = New Helper

End Sub

Save personal.xlsm and restart Excel.

Upvotes: 1

Related Questions