Solved

Add-in to unfilter data in an Excel workbook

Posted on 2006-10-31
6
1,177 Views
Last Modified: 2008-01-09
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
Comment
Question by:cyardley
  • 4
  • 2
6 Comments
 
LVL 35

Expert Comment

by:mvidas
ID: 17842653
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
 
LVL 35

Expert Comment

by:mvidas
ID: 17842730
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
 

Author Comment

by:cyardley
ID: 17843055
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!

 
LVL 35

Accepted Solution

by:
mvidas earned 200 total points
ID: 17843256
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
 

Author Comment

by:cyardley
ID: 17844267
That worked.  

Thanks Matt!  
0
 
LVL 35

Expert Comment

by:mvidas
ID: 17844305
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question