Preventing Save function until Mandatory fields are completed

I heart Excel

Board Regular
Joined
Feb 28, 2011
Messages
66
Hello,

I was posted something earlier, which was very helpful, and was given a Macro that will bring up an error message if not all manadatory fields are filled out on a form I have -

Public LR As Long
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Application.WorksheetFunction.CountA(Sheet1.Cells(LR + 1, "A").Resize(1, 10)) <> 10 Then
MsgBox "Please fill in all Mandatory fields before Saving", vbOKOnly, "Info"

End If
End Sub
Private Sub Workbook_Open()
LR = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row

End Sub

I have had a play around and tried to amend the above to fit into what I am doing.

As it stands I fill in the form, miss out some fields, and the message appears, but I am still able to save.

I need to stop people saving the form until all Mandatory fields are completed.

I need to allow them to either exit the form and not save, or fill out the mandatory fields and save!!

The end users are going to love me for doing this, but needs must!

Thanks :)
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Rich (BB code):
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Application.WorksheetFunction.CountA(Sheet1.Cells(LR + 1, "A").Resize(1, 10)) <> 10 Then
    MsgBox "Please fill in all Mandatory fields before Saving", vbOKOnly, "Info"
    Cancel = True
End If
End Sub
 

I heart Excel

Board Regular
Joined
Feb 28, 2011
Messages
66
Thank you very much, that was so simple yet so affective!!

I don't suppose you would be able to help with the last problem I am having!! I could leave it and just tell the users not to do the following, but if there is a quick and easy solution like the above that would be great.

Basically -

The user fills all but one mandatory field, they go to close the form using the 'X' in the top right hand corner.
They get the option to save, they select 'yes'.
The error appears, telling them to fill out all fields before saving.
They click 'ok' and the form closes, without saving!

I would rather when the error appears and they click 'ok' that it takes them back to the form. Otherwise they will have to fill everything out again.

Am I right in thinking it would be a rule that when 'ok' is selected that it returns to the application?

Thanks again for your help :cool:
 

I heart Excel

Board Regular
Joined
Feb 28, 2011
Messages
66

ADVERTISEMENT

Hello :)

I have managed to find a way around my problem, I have introduced a Message box which will prompt them to fill in the mandatory fields, or the form will close without saving!

Wanted to tweek it a little and am having problems.

Please see below -

Public LR As Long
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not Me.Saved Then
Msg = " Are all Mandatory fields Completed?" & vbCr & "If you Select 'Yes' and they aren't the Form will Close Without Saving and Completed fields will be Lost"
Ans = MsgBox(Msg, vbQuestion + vbYesNo)
Select Case Ans
Case vbYes
Me.Save
Case vbNo
Cancel = True
Exit Sub
End Select

ActiveWorkbook.Saved = True

End If
End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Application.WorksheetFunction.CountA(Sheet1.Cells(LR + 1, "A").Resize(1, 10)) <> 10 Then
MsgBox "Mandatory fields not Completed, unable to Save" & vbCr & " Please Complete Highlighted Fields", vbOKOnly, "Info"
Cancel = True

End If
End Sub


Private Sub Workbook_Open()
LR = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row

End Sub

The bits I have highlighted in Red are those I am trying to 'tweek'!!
I don't want the 'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)' message box appearing if they select Yes when closing, regardless to whether they have completed the fields or not, as there isn't any point in it appearing. I did try adding 'Application.Quit', but this seems to overwrite the Mandatory fields macro and save the form incompleted, which I don't want.

I hope this makes sense, if it doesn't just say and I can try explaining again!!

Thanks
 

Tahas

Board Regular
Joined
Sep 10, 2008
Messages
152
Hi I Heart,

I'm having the similar problem. Could you please post your final solution?

Regards,
Taha
 

Watch MrExcel Video

Forum statistics

Threads
1,108,502
Messages
5,523,295
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top