WorkbookBeforeSave Event - check required fields before saving

[LRay67]

I am not very good with code, I am trying to Save a workbook, but prior to
saving I want it to check certain fields to ensure they are filled in. Below
is the code I am using, but doesn't seem to work. Any Suggestions??

Linda


Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, _
ByVal SaveAsUI As Boolean, Cancel As Boolean)
a = MsgBox("Do you really want to save the workbook?", vbYesNo)
If a = vbYes Then SaveAsUI = True
If TextBox8 = "" Then
MsgBox "Please enter Submitting Agency Name"
Exit Sub
End If
If TextBox1 = "" Then
MsgBox "Please enter Accounting Unit Code (7 Digits)"
Exit Sub
End If
If TextBox4 = "" Then
MsgBox "Please enter Task Coordinator Name"
Exit Sub
End If
If TextBox5 = "" Then
MsgBox "Please enter Task Coordinator Telephone Number"
Exit Sub
End If
If TextBox6 = "" Then
MsgBox "Please enter Task Coordinator Email Address"
Exit Sub
End If
End Sub

 

[cht13er]

I am not very good with code, I am trying to Save a workbook, but prior to
saving I want it to check certain fields to ensure they are filled in.  Below
is the code I am using, but doesn't seem to work.  Any Suggestions??

Linda

Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, _
        ByVal SaveAsUI As Boolean, Cancel As Boolean)
  a = MsgBox("Do you really want to save the workbook?", vbYesNo)
    If a = vbYes Then SaveAsUI = True
 If TextBox8 = "" Then
    MsgBox "Please enter Submitting Agency Name"
    Exit Sub
    End If
If TextBox1 = "" Then
    MsgBox "Please enter Accounting Unit Code (7 Digits)"
    Exit Sub
    End If
 If TextBox4 = "" Then
    MsgBox "Please enter Task Coordinator Name"
    Exit Sub
    End If
 If TextBox5 = "" Then
    MsgBox "Please enter Task Coordinator Telephone Number"
    Exit Sub
    End If
 If TextBox6 = "" Then
    MsgBox "Please enter Task Coordinator Email Address"
    Exit Sub
    End If
 End Sub

Try this ....

Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, _
ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim bnSave as Boolean

a = MsgBox("Do you really want to save the workbook?", vbYesNo)
If a = vbYes Then bnSave = True

If TextBox8 = "" Then
MsgBox "Please enter Submitting Agency Name"
Exit Sub
End If

If TextBox1 = "" Then
MsgBox "Please enter Accounting Unit Code (7 Digits)"
Exit Sub
End If

If TextBox4 = "" Then
MsgBox "Please enter Task Coordinator Name"
Exit Sub
End If

If TextBox5 = "" Then
MsgBox "Please enter Task Coordinator Telephone Number"
Exit Sub
End If

If TextBox6 = "" Then
MsgBox "Please enter Task Coordinator Email Address"
Exit Sub
End If

If bnSave = True then
ActiveWorkbook.Save
End if

End Sub




Does that help??

Chris

 

[LRay67]

Chris, I am not even getting the 1st message box "Do you really want to save
the workbook?" Nothing is working....Is there a particular place in the code
sheet that I am to place the Private Sub App_WorkbookBeforeSave statement?
Help -Thanks

 

[cht13er]

Chris, I am not even getting the 1st message box "Do you really want to save
the workbook?" Nothing is working....Is there a particular place in the code
sheet that I am to place the Private Sub App_WorkbookBeforeSave statement? 
Help  -Thanks


















- Show quoted text -

Aha! Place this in the "ThisWorkbook" area:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim bnSave As Boolean

a = MsgBox("Do you really want to save the workbook?", vbYesNo)
If a = vbYes Then bnSave = True


If TextBox8 = "" Then
MsgBox "Please enter Submitting Agency Name"
Exit Sub
End If


If TextBox1 = "" Then
MsgBox "Please enter Accounting Unit Code (7 Digits)"
Exit Sub
End If


If TextBox4 = "" Then
MsgBox "Please enter Task Coordinator Name"
Exit Sub
End If


If TextBox5 = "" Then
MsgBox "Please enter Task Coordinator Telephone Number"
Exit Sub
End If


If TextBox6 = "" Then
MsgBox "Please enter Task Coordinator Email Address"
Exit Sub
End If

If bnSave = True Then
SaveAsUI = True
End If


End Sub