Combining Code - Won't WORK!

[dogsoul]

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???

[shg]

Please take a few minutes to read the forum rules (link in menu bar).

Then edit your post to amend your thread title and add code tags.

[shg]

Leto, please take a few minutes to read the forum rules about posting in threads with pending moderation.

[leto2]

You must have posted after I had already begun writing an answer. Sorry.

[DonkeyOte]

Tags have still not been added and in addition thread has now been duplicated, as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.