I've got the following code that will automatically UNFilter a worksheet before it saves.... works great & looks like this:


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 since I can't FORCE people to enable Macros - I found the following code which will take them to a welcome screen & won't show the worksheet UNTIL macros get enabled.... cool workaround - instructions are here followed by the code:

- 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


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

Problem is that when I try to combine these in the THIS WORKBOOK section - it tells me I've got an 'AMBIGUOUS NAME DETECTED' under Workspace_Save.... ok, I see that - but I can't just rename one of them - and my attempts to 'combine' these into 1 code have failed miserably (I'm a novice) - anybody got any ideas out there???