Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Add-in to unfilter data in an Excel workbook

Posted on 2006-10-31
6
Medium Priority
?
1,194 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

618 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