I'm currently creating a document that would allow supervisors to audit their people.
I'm looking for a way to prevent saving if cells I and J are not completed if the person failed the previous audit.
Here's photo of what my current document looks like to kind of get an idea:
I have a formula set in column H to put the phrase
Fail if a certain point range is not met:
I need to make it that they have to fill in all data after this if the associate doesn't reach that 85% point goal. The following code is what I have so far but I cannot get it to work properly. Any changes or new code is happily accepted.
- Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
- With Sheets("5s Report Dec 2019")
- If WorksheetFunction(.Range("H:H")).Value = "Pass" Then
- Cancel = False
- If WorksheetFunction.CountA(.Range("H:H")) <> WorksheetFunction.CountA(.Range("I:I")) / 2 Then
- Cancel = True
- MsgBox "Please enter a values in columns I and J", vbCritical, "Error!"
- End If
- End If
- End With
- End Sub
technically it would only be the cell of H4 but I need it for the entire column as this would be an ongoing document for the month. so the usage would be you would select the person being audited what machine they are working on Column F would produce the points possible you would enter the Points scored on column E G would give your percentage and the formula in H would generate your Pass Or Fail value. if Value H is Fail I would like it to lock out saving untill the Fill in Column I and J.