Preventing Save function until Mandatory fields are completed

[I heart Excel]

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

 

[VoG]

Try

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]

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

 

[VoG]

Sorry, I can't think of a way round that.

 

[I heart Excel]

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

 

[I heart Excel]

Not to worry, my persistence paid off!!

Thanks

 

[Tahas]

Hi I Heart,

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

Regards,
Taha