Workbook_BeforeSave macro not working

**jfarlow** · 02-19-2009, 05:41 AM

I am trying to force users to enter certain information in a spreadsheet, and if they don't enter it they won't be able to save the spreadsheet. e.g. if a bonus recommendation is over a certain threshold they have to enter an explanation. if they don't enter the explanation I want it to display a message and prevent them from saving the file.

This is what I have at the moment...there are 3 different instances where I want the save to be prevented, but at the moment the messages display (I've entered test data) but the workbook still saves. any ideas what I'm doing wrong?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Worksheets("Protected Data")
If .Range("BL3").Value > 0 Then MsgBox "YOU MUST ENTER AN EXPLANATION FOR ANY RECOMMENDATION ABOVE THE GUIDELINES LIMITS"
Cancel = True
If .Range("BL3").Value = 0 Then Cancel = False

If .Range("DP3").Value > 0 Then MsgBox "ONE-UP RATING MUST BE ENTERED FOR ALL EMPLOYEES WITH A RECOMMENDATION"
Cancel = True
If .Range("DP3").Value = 0 Then Cancel = False

If .Range("DO927").Value > 0 Then MsgBox "YOU MUST ENTER A COUNTRY FOR ALL CONTRIBUTIONS YOU ARE MAKING TO OTHER BU'S"
Cancel = True
If .Range("DO927").Value = 0 Then Cancel = False
End With
End Sub

**DonkeyOte** · 02-19-2009, 05:48 AM

Please edit your post and put your VBA into CODE tags -- use the # icon.

The problem you have is that for ex. BL3 value > 0 but DO927 is = 0 then the Cancel will be set to False, ie file saved.

You should think about leaving the tests as and when a Cancel = True is established... ie

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Worksheets("Protected Data")
    If .Range("BL3").Value > 0 Then
        MsgBox "YOU MUST ENTER AN EXPLANATION FOR ANY RECOMMENDATION ABOVE THE GUIDELINES LIMITS"
        Cancel = True
        Exit Sub
    End If

    If .Range("DP3").Value > 0 Then
        MsgBox "ONE-UP RATING MUST BE ENTERED FOR ALL EMPLOYEES WITH A RECOMMENDATION"
        Cancel = True
        Exit Sub
    End If
    
    If .Range("DO927").Value > 0 Then
        MsgBox "YOU MUST ENTER A COUNTRY FOR ALL CONTRIBUTIONS YOU ARE MAKING TO OTHER BU'S"
        Cancel = True
        Exit Sub
    End If
End With
End Sub

Workbook_BeforeSave macro not working

LinkBack

Thread Tools

Display

Hybrid View

Workbook_BeforeSave macro not working

Re: Workbook_BeforeSave macro not working

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions