Improve company productivity with a Business Account.Sign Up

x
?
Solved

Add-in to unfilter data in an Excel workbook

Posted on 2006-10-31
6
Medium Priority
?
1,218 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
LVL 35

Accepted Solution

by:
mvidas earned 800 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
As a person who answers a lot of questions, I often see code that could be simplified, made easier to read, and perhaps most importantly made easier to maintain if the code was modified to use the Select Case statement. This article explains how to…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

595 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