PDA

View Full Version : Macro To Not Allow Saving As When Specific Fields Are Left Blank



rachell_8
09-22-2010, 12:06 AM
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! :help

Bob Phillips
09-22-2010, 01:36 AM
Just use the BeforeSave event instead of the BeforePrint event.

rachell_8
09-22-2010, 11:08 PM
Thanks xld but it does not work for me. I tried it. I dunno whats wrong..

Bob Phillips
09-23-2010, 01:14 AM
What did you try?

rachell_8
09-23-2010, 07:09 AM
I tried this code :

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

Bob Phillips
09-23-2010, 07:24 AM
I just plugged that code into your workbook and it worked fine.