Macro To Not Allow Saving As When Specific Fields Are Left Blank

[rachell_8]

Hi, can someone please help me create a macro on my file. I want the user to not be able to save the file unless he has filled-out the fields I have tagged in red font. I want an error message to prompt user that the required fields should be populated first before saving as.

I have posted a similar question before but that is on printing. This time I would like to prevent Saving As when my required fields are not complete filled-out.

http://www.vbaexpress.com/forum/showthread.php?t=34056

Thanks in advance!

[Bob Phillips]

Just use the BeforeSave event instead of the BeforePrint event.

[rachell_8]

Thanks xld but it does not work for me. I tried it. I dunno whats wrong..

[Bob Phillips]

What did you try?

[rachell_8]

I tried this code :
[VBA]
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim msg As String

With ActiveSheet

If .Range("I19").Value2 = "" Then

msg = msg & vbTab & "Missing Vendor Name" & vbNewLine
End If

If .Range("C29").Value2 = "" Then

msg = msg & vbTab & "Missing Street House Number" & vbNewLine
End If

'etc

If msg <> "" Then

msg = "Missing items: " & vbNewLine & vbNewLine _
& msg & vbNewLine & vbNewLine _
& "Correct and resubmit!!!" & vbNewLine & vbNewLine

MsgBox msg, vbExclamation, "Save As Failure"
Cancel = True
End If
End With
End Sub[/VBA]

[Bob Phillips]

I just plugged that code into your workbook and it worked fine.