'If' function for amending mandatory fields

[I heart Excel]

Hello,

I have a spreadsheet that other members of staff complete. I have a 11 headers and each header is a mandatory field. The user fills in the spreadsheet with new items to be added to our internal purchasing system.

Everytime the form is opened the user can't save until all required fields are complete.

I do not want this to run unless the user fills in a particular field, such as product code. This is because sometimes the user will open the spreadsheet and amend information they have already added, but are unable to save as the form is assuming they haven't completed the required information for the next row down.

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 & " 'Yes' will Save the Form" & 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
Application.Quit

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, 11)) <> 11 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

Is anyone able to help?

Thanks

 

[sijpie]

I don't know about the form you are using to enter and edit the data. Is it on the sheet or is it in a proper userform? From your exit code I am assuming the user enters/edits data on the sheet.

There are several ways in which this can be tackled. The cleanest (also for entering data and checking input is correct) is to use a Userform.

Next is to make a check as the user is entering or editing data on the sheet, using the Worksheet_Change() event.
Here you can trap which cell has been changed and check to see if all the data in the same row has been entered (by counting the clees with entry for instance). If all OK, a flag field is set to true, if fields are still empty for the last edited row, the flagfield is set to False.

Then if the sheet is closed, only the value in this Flagcell needs to be checked.

 

[RSXchin]

don't forget to use code tags [ code ] and [ /code ] or [ php ] and [ /php]


no spaces