I am using Excel 2000, sp2 and I have created a userform that is called in
the Before Save event to provide the reminders to the user. When the
spreadsheet is saved, however, the userform box is appearing twice. Help!


Private Sub CommandButton1_Click()
Unload Reminders
End Sub

Public Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim UserFileName As Variant
Cancel = True
SaveAsUI = True
Worksheets("Accrual Form").Activate
If Range("m9").Value 0 Then
MsgBox "Invalid account coding entered. Please correct before
saving."
ElseIf Range("n9").Value 0 Then
MsgBox "Monetary amount missing. Please correct before saving."
ElseIf Range("o9").Value 0 Then
MsgBox "Incorrect monetary amount entered. Please correct
before saving."
ElseIf Range("p9").Value 0 Then
MsgBox "Zero monetary amount entered. Please correct before
saving."
ElseIf Range("q9").Value 0 Then
MsgBox "Stat amount missing. Please correct before saving."
ElseIf Range("r9").Value 0 Then
MsgBox "Incorrect stat amount entered. Please correct before
saving."
ElseIf Range("s9").Value 0 Then
MsgBox "Zero stat amount entered. Please correct before saving."
ElseIf Range("t9").Value 0 Then
MsgBox "Stat amount entered for monetary account. Please
correct before saving."
ElseIf Range("w9").Value 0 Then
MsgBox "Negative monetary amount entered with positive stat
amount (or vice versa). Please correct before saving."
ElseIf Range("x9").Value 0 Then
MsgBox "Business unit number missing or invalid. Please correct
before saving."
ElseIf Range("y9").Value 0 Then
MsgBox "Monetary amount entered with no account coding. Please
correct before saving."
ElseIf Range("z9").Value 0 Then
MsgBox "No accrual information entered. Please correct before
saving."
ElseIf Range("aa9").Value 0 Then
MsgBox "Amount entered with more than two decimal places.
Please correct before saving."
ElseIf Range("ab9").Value 0 Then
MsgBox "'$ Amount' equal to 'Stat Amount'. Stat amounts should
reflect number of hours worked, rather than dollar amount paid. Please
correct before saving."
ElseIf Date - Range("d4").Value 7 Then
MsgBox "Incorrect template for accrual month. Please contact
your RVP or RFM for additional instructions."
Else '(IF TEMPLATE PASSES ALL VALIDITY CHECKS)
If Not (Application.WorksheetFunction.IsNA(Range("e5").Va lue))
Then '(AND IF DATA ENTERED)
If ThisWorkbook.Name < Right(Range("e5").Value, 25) Then
'(IF FILE IS NOT THE REQUIRED FILE)
If ThisWorkbook.Name =
"SAVA_Facility_AP_Accrual_Template.xls" Then '(IF FILE HAS NOT BEEN SAVED)
UserFileName =
Application.GetSaveAsFilename(Range("e5").Value) '(PROMPT FOR FILENAME)
Application.ScreenUpdating = False
If UserFileName < False Then
ActiveWorkbook.SaveCopyAs (UserFileName) '(SAVE
AS USER'S FILENAME)
If UserFileName < Range("e5").Value Then
ActiveWorkbook.SaveCopyAs
Filename:=Range("e5").Value '(SAVE AS REQUIRED FILENAME, IF USER DID NOT)
End If
Workbooks.Open Filename:=UserFileName '(OPEN
USER'S FILE)
ThisWorkbook.Close savechanges:=False ' (CLOSE
TEMPLATE FILE)
Else
Application.ScreenUpdating = True
MsgBox "Please enter a filename to save the
file." '(IF USER CANCELS GETSAVEASFILENAME)
Exit Sub
End If
Else '(IF USER'S FILE)
Application.ScreenUpdating = False
ActiveWorkbook.Save '(UPDATE USER'S FILE)
ActiveWorkbook.SaveCopyAs (Range("E5").Value)
'(UPDATE REQUIRED FILE)
Cancel = False
End If
Else '(IF REQUIRED FILE)
Application.ScreenUpdating = False
ActiveWorkbook.Save '(UPDATE REQUIRED FILE)
Cancel = False
End If
Load Reminders
Reminders.Show
Else
Cancel = False '(IF TEMPLATE PASSES VALIDITY CHECKS AND IS
BLANK, SAVE TEMPLATE FORM)
End If
End If
Application.ScreenUpdating = True
End Sub

Emily,
Whilst you use cancel=true so the save dialog is NOT shown for the current
cycle of the code, you call .Save/.SaveAs which fires the event again.
You can avoid this by setting .EnableEvent=false before you call these
methods, then set back to true after.

NickHK

"Emily Edgington" <Emily wrote in
message ...
I am using Excel 2000, sp2 and I have created a userform that is called in
the Before Save event to provide the reminders to the user. When the
spreadsheet is saved, however, the userform box is appearing twice. Help!


Private Sub CommandButton1_Click()
Unload Reminders
End Sub

Public Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim UserFileName As Variant
Cancel = True
SaveAsUI = True
Worksheets("Accrual Form").Activate
If Range("m9").Value 0 Then
MsgBox "Invalid account coding entered. Please correct before
saving."
ElseIf Range("n9").Value 0 Then
MsgBox "Monetary amount missing. Please correct before
saving."
ElseIf Range("o9").Value 0 Then
MsgBox "Incorrect monetary amount entered. Please correct
before saving."
ElseIf Range("p9").Value 0 Then
MsgBox "Zero monetary amount entered. Please correct before
saving."
ElseIf Range("q9").Value 0 Then
MsgBox "Stat amount missing. Please correct before saving."
ElseIf Range("r9").Value 0 Then
MsgBox "Incorrect stat amount entered. Please correct before
saving."
ElseIf Range("s9").Value 0 Then
MsgBox "Zero stat amount entered. Please correct before
saving."
ElseIf Range("t9").Value 0 Then
MsgBox "Stat amount entered for monetary account. Please
correct before saving."
ElseIf Range("w9").Value 0 Then
MsgBox "Negative monetary amount entered with positive stat
amount (or vice versa). Please correct before saving."
ElseIf Range("x9").Value 0 Then
MsgBox "Business unit number missing or invalid. Please
correct
before saving."
ElseIf Range("y9").Value 0 Then
MsgBox "Monetary amount entered with no account coding.
Please
correct before saving."
ElseIf Range("z9").Value 0 Then
MsgBox "No accrual information entered. Please correct before
saving."
ElseIf Range("aa9").Value 0 Then
MsgBox "Amount entered with more than two decimal places.
Please correct before saving."
ElseIf Range("ab9").Value 0 Then
MsgBox "'$ Amount' equal to 'Stat Amount'. Stat amounts
should
reflect number of hours worked, rather than dollar amount paid. Please
correct before saving."
ElseIf Date - Range("d4").Value 7 Then
MsgBox "Incorrect template for accrual month. Please contact
your RVP or RFM for additional instructions."
Else '(IF TEMPLATE PASSES ALL VALIDITY CHECKS)
If Not (Application.WorksheetFunction.IsNA(Range("e5").Va lue))
Then '(AND IF DATA ENTERED)
If ThisWorkbook.Name < Right(Range("e5").Value, 25) Then
'(IF FILE IS NOT THE REQUIRED FILE)
If ThisWorkbook.Name =
"SAVA_Facility_AP_Accrual_Template.xls" Then '(IF FILE HAS NOT BEEN SAVED)
UserFileName =
Application.GetSaveAsFilename(Range("e5").Value) '(PROMPT FOR FILENAME)
Application.ScreenUpdating = False
If UserFileName < False Then
ActiveWorkbook.SaveCopyAs (UserFileName)
'(SAVE
AS USER'S FILENAME)
If UserFileName < Range("e5").Value Then
ActiveWorkbook.SaveCopyAs
Filename:=Range("e5").Value '(SAVE AS REQUIRED FILENAME, IF USER DID NOT)
End If
Workbooks.Open Filename:=UserFileName '(OPEN
USER'S FILE)
ThisWorkbook.Close savechanges:=False ' (CLOSE
TEMPLATE FILE)
Else
Application.ScreenUpdating = True
MsgBox "Please enter a filename to save the
file." '(IF USER CANCELS GETSAVEASFILENAME)
Exit Sub
End If
Else '(IF USER'S FILE)
Application.ScreenUpdating = False
ActiveWorkbook.Save '(UPDATE USER'S FILE)
ActiveWorkbook.SaveCopyAs (Range("E5").Value)
'(UPDATE REQUIRED FILE)
Cancel = False
End If
Else '(IF REQUIRED FILE)
Application.ScreenUpdating = False
ActiveWorkbook.Save '(UPDATE REQUIRED FILE)
Cancel = False
End If
Load Reminders
Reminders.Show
Else
Cancel = False '(IF TEMPLATE PASSES VALIDITY CHECKS AND IS
BLANK, SAVE TEMPLATE FORM)
End If
End If
Application.ScreenUpdating = True
End Sub

Thanks Nick - works great!

"NickHK" wrote:

Emily,
Whilst you use cancel=true so the save dialog is NOT shown for the current
cycle of the code, you call .Save/.SaveAs which fires the event again.
You can avoid this by setting .EnableEvent=false before you call these
methods, then set back to true after.

NickHK

"Emily Edgington" <Emily wrote in
message ...
I am using Excel 2000, sp2 and I have created a userform that is called in
the Before Save event to provide the reminders to the user. When the
spreadsheet is saved, however, the userform box is appearing twice. Help!


Private Sub CommandButton1_Click()
Unload Reminders
End Sub

Public Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim UserFileName As Variant
Cancel = True
SaveAsUI = True
Worksheets("Accrual Form").Activate
If Range("m9").Value 0 Then
MsgBox "Invalid account coding entered. Please correct before
saving."
ElseIf Range("n9").Value 0 Then
MsgBox "Monetary amount missing. Please correct before
saving."
ElseIf Range("o9").Value 0 Then
MsgBox "Incorrect monetary amount entered. Please correct
before saving."
ElseIf Range("p9").Value 0 Then
MsgBox "Zero monetary amount entered. Please correct before
saving."
ElseIf Range("q9").Value 0 Then
MsgBox "Stat amount missing. Please correct before saving."
ElseIf Range("r9").Value 0 Then
MsgBox "Incorrect stat amount entered. Please correct before
saving."
ElseIf Range("s9").Value 0 Then
MsgBox "Zero stat amount entered. Please correct before
saving."
ElseIf Range("t9").Value 0 Then
MsgBox "Stat amount entered for monetary account. Please
correct before saving."
ElseIf Range("w9").Value 0 Then
MsgBox "Negative monetary amount entered with positive stat
amount (or vice versa). Please correct before saving."
ElseIf Range("x9").Value 0 Then
MsgBox "Business unit number missing or invalid. Please
correct
before saving."
ElseIf Range("y9").Value 0 Then
MsgBox "Monetary amount entered with no account coding.
Please
correct before saving."
ElseIf Range("z9").Value 0 Then
MsgBox "No accrual information entered. Please correct before
saving."
ElseIf Range("aa9").Value 0 Then
MsgBox "Amount entered with more than two decimal places.
Please correct before saving."
ElseIf Range("ab9").Value 0 Then
MsgBox "'$ Amount' equal to 'Stat Amount'. Stat amounts
should
reflect number of hours worked, rather than dollar amount paid. Please
correct before saving."
ElseIf Date - Range("d4").Value 7 Then
MsgBox "Incorrect template for accrual month. Please contact
your RVP or RFM for additional instructions."
Else '(IF TEMPLATE PASSES ALL VALIDITY CHECKS)
If Not (Application.WorksheetFunction.IsNA(Range("e5").Va lue))
Then '(AND IF DATA ENTERED)
If ThisWorkbook.Name < Right(Range("e5").Value, 25) Then
'(IF FILE IS NOT THE REQUIRED FILE)
If ThisWorkbook.Name =
"SAVA_Facility_AP_Accrual_Template.xls" Then '(IF FILE HAS NOT BEEN SAVED)
UserFileName =
Application.GetSaveAsFilename(Range("e5").Value) '(PROMPT FOR FILENAME)
Application.ScreenUpdating = False
If UserFileName < False Then
ActiveWorkbook.SaveCopyAs (UserFileName)
'(SAVE
AS USER'S FILENAME)
If UserFileName < Range("e5").Value Then
ActiveWorkbook.SaveCopyAs
Filename:=Range("e5").Value '(SAVE AS REQUIRED FILENAME, IF USER DID NOT)
End If
Workbooks.Open Filename:=UserFileName '(OPEN
USER'S FILE)
ThisWorkbook.Close savechanges:=False ' (CLOSE
TEMPLATE FILE)
Else
Application.ScreenUpdating = True
MsgBox "Please enter a filename to save the
file." '(IF USER CANCELS GETSAVEASFILENAME)
Exit Sub
End If
Else '(IF USER'S FILE)
Application.ScreenUpdating = False
ActiveWorkbook.Save '(UPDATE USER'S FILE)
ActiveWorkbook.SaveCopyAs (Range("E5").Value)
'(UPDATE REQUIRED FILE)
Cancel = False
End If
Else '(IF REQUIRED FILE)
Application.ScreenUpdating = False
ActiveWorkbook.Save '(UPDATE REQUIRED FILE)
Cancel = False
End If
Load Reminders
Reminders.Show
Else
Cancel = False '(IF TEMPLATE PASSES VALIDITY CHECKS AND IS
BLANK, SAVE TEMPLATE FORM)
End If
End If
Application.ScreenUpdating = True
End Sub