Add-in to unfilter data in an Excel workbook

Does anyone know of VBA code that I can write to programmatically unfilter an autofiltered worksheet before closing a workbook?  My problem is that I have to do this as an add-in not a macro.  It will be used in multiple workbooks.  I currently have the following code in my .xla file and I know I'm missing something.  The only action that is being performed is the Activeworkbook.save

Public Sub Workbook_BeforeClose(Cancel As Boolean)
On Error GoTo Workbook_BeforeCloseErr
         
                         
    For Each ws In Worksheets
       If ws.FilterMode = True Then
           ws.ShowAllData
       End If
    Next ws

     ActiveWorkbook.Save
         

Workbook_BeforeCloseExit:
Exit Sub
Workbook_BeforeCloseErr:
MsgBox Err & ": " & Error
Resume Next
End Sub


Thanks in advance for any help that you can give!
cyardleyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
mvidasConnect With a Mentor Commented:
There shouldnt be any references needed, though you have to make sure these two lines are above any subs/functions:

Option Explicit
Private WithEvents ThisApp As Excel.Application

The code gets run when the add-in is opened. If you're putting it into an existing add-in (without restarting), put the cursor somewhere in the workbook_open event and press F5 to manually run it.  The ThisApp variable needs to be assigned before the _workbookbeforeclose event will fire.  Sorry about that.
Matt
0
 
mvidasCommented:
Hello,

 If (WS.FilterMode Or WS.AutoFilterMode) Then WS.AutoFilterMode = False

However if you have this in an add-in then this _beforeclose event will only fire when the add-in is closed.  You can write application-level events (to catch any workbook being closed), would you like any more information on that?

Matt
0
 
mvidasCommented:
I know I didn't give you any time to answer if you wanted information, but I figured I'd post it anyways.  Put this into the ThisWorkbook object of your add-in:


Option Explicit
Private WithEvents ThisApp As Excel.Application

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 Set ThisApp = Nothing
 If Not ThisWorkbook.Saved Then ThisWorkbook.Save
End Sub

Private Sub Workbook_Open()
 Set ThisApp = Application
End Sub

Private Sub ThisApp_WorkbookBeforeClose(ByVal WB As Workbook, Cancel As Boolean)
 On Error GoTo Workbook_BeforeCloseErr
 Dim WS As Worksheet

 For Each WS In WB.Worksheets
  If (WS.FilterMode Or WS.AutoFilterMode) Then
   WS.ShowAllData
   WS.AutoFilterMode = False
  End If
 Next WS
 If Not WB.Saved Then WB.Save
 Exit Sub

Workbook_BeforeCloseErr:
 MsgBox Err & ": " & Error
 Resume Next
End Sub


Matt
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
cyardleyAuthor Commented:
Matt,

Thanks for the quick reply.  

I'm sure I'm doing something incorrect but that did not work when I put that in the ThisWorkbook object of my Add-in.  Are there any references that I need to make sure are applied?  

Chuck
0
 
cyardleyAuthor Commented:
That worked.  

Thanks Matt!  
0
 
mvidasCommented:
Glad to help! I'm sure you can figure out how you'd add more application-level events, but please don't hesitate to ask if you want to know how it works or would like help adding any more.

Matt
0
All Courses

From novice to tech pro — start learning today.