Auto UNfilter Excel before saving

[dogsoul]

Please help me retain my sanity...

Please note - I am NOT a programmer so don't be shy about talking to me like I am 6. I AM decent with Excel - at least I was until they decided to throw the entire deck of cards up in the air for their 2007 unveiling...

Anyway - I'm creating a shared workbook on a server which has a filter component included. Users will be using the filters, adding info, and most probably SAVING while it's still filtered. I want to have Excel UNFILTER the worksheet automatically upon saving so that the next user views the UNfiltered worksheet.

I went online & found the following:


"Here is some code that you can put in the ThisWorkbook event module to expand all the filters on the active worksheet before saving. If you want to target a specific worksheet other than the active worksheet just replace ActiveSheet with Worksheets("Your worksheet name") in the code below.

Please Login or Register  to view this content.

To install this code simply right-click on the Excel icon at the left end of the Excel Worksheet Menu Bar, select View Code, and paste the above code into the VBE code pane."

Now maybe I'm just numb - but I BELIEVE I put the above code in the correct place & all.... and absolutely nothing happened - file still saves with filters in place...

Someone please help me figure this out before I put my foot though this monitor... and again, please spell it out to me like I'm a small rodent such as a ferret or a squirrel. Thanks.

[stanleydgromjr]

dogsoul,

This is not tested.

Try:

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

Copy the below code, and paste it into VBAProject, Microsoft Excel Objects, ThisWorkbook (on the right pane).

Please Login or Register  to view this content.

[dogsoul]

...maybe I'm not posting this code in the correct way. Here's what I did.

Using Excel 2007:

I copied your code - went to the 'DEVELOPER' section on the menu bar - went to the 'VIEW CODE' button - pasted it into the pane - saved the worksheet - closed it out - reopened the worksheet - filtered one of the columns - saved it again - closed out - reopened.... and the the worksheet was STILL filtered.

Am I not 'saving' the code properly to get it to work?

[shg]

Select ThisWorkbook from the Project window, and then paste the code there.

Doing this on saving will clobber all the user's filter settings. Is that OK?

[dogsoul]

I don't get it.... I really don't. I pasted that code into the panel under THIS WORKBOOK - saved the file - filtered it - saved it again - closed out - and when I reopened the file... sure enough, it was still filtered.

I've got 2007 NON Vista - is THAT my problem? Is there something else I need to do in order to 'activate' the code once I paste it in besides just saving the file?

[shg]

I haven't tried Stan's code. Try this:

Please Login or Register  to view this content.

The Stop line is to find out if the code is triggering, and will be deleted later.

[dogsoul]

Thanks for the help folks... it's starting to work. I've still gone thru these odd scenerios where it won't work, won't work, won't work - then suddenly it works & who the heck knows why...

Anyway, here's where I'm at now. I've implemented the following code successfully to UNfilter results before saving:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Expand (turn off) all filters on the active worksheet
Dim Fltr As Filter
Dim iFiltr As Integer
With ActiveSheet.AutoFilter
For iFiltr = 1 To .Filters.Count
If .Filters(iFiltr).On Then
.Range.AutoFilter field:=iFiltr
End If
Next iFiltr
End With
End Sub

Now - here's the tricky part (for me anyway)...

I read up on what it takes to effectively FORCE people to enable macros - and found that it's really next to impossible - or at least not practical. So I found the following code from a guy who essentially created some kind of 'welcome' screen where you HAVE to enable macros in order to access the worksheets of interest:

Option Explicit

Const WelcomePage = "Macros"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Turn off events to prevent unwanted loops
Application.EnableEvents = False

'Evaluate if workbook is saved and emulate default propmts
With ThisWorkbook
If Not .Saved Then
Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _
vbYesNoCancel + vbExclamation)
Case Is = vbYes
'Call customized save routine
Call CustomSave
Case Is = vbNo
'Do not save
Case Is = vbCancel
'Set up procedure to cancel close
Cancel = True
End Select
End If

'If Cancel was clicked, turn events back on and cancel close,
'otherwise close the workbook without saving further changes
If Not Cancel = True Then
.Saved = True
Application.EnableEvents = True
.Close savechanges:=False
Else
Application.EnableEvents = True
End If
End With
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Turn off events to prevent unwanted loops
Application.EnableEvents = False

'Call customized save routine and set workbook's saved property to true
'(To cancel regular saving)
Call CustomSave(SaveAsUI)
Cancel = True

'Turn events back on an set saved property to true
Application.EnableEvents = True
ThisWorkbook.Saved = True
End Sub

Private Sub Workbook_Open()
'Unhide all worksheets
Application.ScreenUpdating = False
Call ShowAllSheets
Application.ScreenUpdating = True
End Sub

Private Sub CustomSave(Optional SaveAs As Boolean)
Dim ws As Worksheet, aWs As Worksheet, newFname As String
'Turn off screen flashing
Application.ScreenUpdating = False

'Record active worksheet
Set aWs = ActiveSheet

'Hide all sheets
Call HideAllSheets

'Save workbook directly or prompt for saveas filename
If SaveAs = True Then
newFname = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
Else
ThisWorkbook.Save
End If

'Restore file to where user was
Call ShowAllSheets
aWs.Activate

'Restore screen updates
Application.ScreenUpdating = True
End Sub

Private Sub HideAllSheets()
'Hide all worksheets except the macro welcome page
Dim ws As Worksheet

Worksheets(WelcomePage).Visible = xlSheetVisible

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden
Next ws

Worksheets(WelcomePage).Activate
End Sub

Private Sub ShowAllSheets()
'Show all worksheets except the macro welcome page

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
Next ws

Worksheets(WelcomePage).Visible = xlSheetVeryHidden
End Sub

And here's his instructions:


How to use:

Rename a worksheet in your workbook to "Macros".
Put a message on the page telling the user to enable macros.
Copy above code.
In Excel press Alt + F11 to enter the VBE.
Press Ctrl + R to show the Project Explorer.
In the project explorer, locate the ThisWorkbook object.
Double click the ThisWorkbook object.
Paste code into the right pane.
Press Alt + Q to close the VBE.
Save workbook before any other changes.
Close and reopen the workbook.


Test the code:

If you reopen the workbook with macros disabled, you should just see your warning screen.
If you reopen the workbook with macros enabled, you should see your warning screen for a second, then you should see all of your other sheets and the warning screen will dissappear.


Ok - so I'm sure this is the NOVICE in me... but I tested it out on its own & it works great, but when I tried to paste the above code into the same 'ThisWorkbook' section in the VBA Project pane, it gave me the following error:

Compile Error
Ambiguous Name Detected
Workbook_BeforeSave

I tried doing my own armchair altering of names.... which was an unmitigated disaster (thanks for the advice to back up my files btw) - so what should I do? I assume I can add multiple sets of code into the same 'ThisWorkbook' pane - but how do I avoid this conflict & get them both to work?