[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1202
  • Last Modified:

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!
0
cyardley
Asked:
cyardley
  • 4
  • 2
1 Solution
 
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mvidasCommented:
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now