Macro to save the document

[LILI]

Hello, I wouldlike to know if anyone knows if its possible to make a macro in
order to not save an excel document if when filling a form the information is
not complete.

Ex.: i made an application form that ask you: a) your name b) your age c)
your birth date - what I need is to program the document so if you dont fill
out all the information you can not save it.

I appreciate your help...

 

[Gord Dibben]

In Thisworkbook module.

Private Sub Workbook_BeforeSave _
(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim rng As Range
Set rng = Sheets("Sheet1").Range("A1:A3") 'adjust to suit
If Application.WorksheetFunction.CountA(rng) < 3 Then
MsgBox "fill in all required cells"
Cancel = True
End If
End Sub


Gord Dibben MS Excel MVP

 

[Per Jessen]

Hi

It can be done like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Set TargetSheet = Worksheets("Sheet1")
Set TargetRange = TargetSheet.Range("A1:A3")
If WorksheetFunction.CountBlank(TargetRange) <> 0 Then
Cancel = True
msg = MsgBox("You have not supplied the required information" _
& vbLf & vbLf & _
"Please fill in the required information before you save the
form" _
, vbCritical + vbOKOnly)
End If
End Sub

This is event code, which has to go into the codesheet for
ThisWorkbook.

Regards,
Per