New (ish) to VBA

[Chancer]

I have managed to create a macro that prevents saving of a workbook if certain cells are not populated, I get a nice polite message prompting the user to return and complete the form, however, when criteria have been met and I press the button to run the macro again nothing happens. Do I need some sort of reset?

Sub Macro2()
' Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' Macro2 Macro

' Checks number of "Fails" against number of "Details of Issue/Finding Identified" and if mismatch stop save
If Worksheets("MSAC").Range("g38") < Worksheets("MSAC").Range("f37") Then
Cancel = True
MsgBox "You cannot save until. All required 'Details of Issue / Finding Identified' cells are completed", vbOKOnly
End If

' Checks number of "Fails" against number of "Action Required to Remediate" and if mismatch stop save
If Worksheets("MSAC").Range("h38") < Worksheets("MSAC").Range("f37") Then
Cancel = True
MsgBox "You cannot save until. All required 'Action Required to Remediate' cells are completed", vbOKOnly
End If

' Checks number of "Fails" against number of "Remediation Due Date" and if mismatch stop save
If Worksheets("MSAC").Range("i38") < Worksheets("MSAC").Range("f37") Then
Cancel = True
MsgBox "You cannot save until. All required 'Remediation Due Date' cells are completed", vbOKOnly
End If

' Check completion before progress to Save & Send

Dim c As Range

For Each c In Range("J38")
If c.Value = 0 Then Exit Sub
Next c

 

[bebo021999]

For Each c In Range("J38")
If c.Value = 0 Then Exit Sub
Next c

Range("J38") is a single cell, you may need a range, like this, for instant, Range("J38: J100"), to loop c. value through

 

[Chancer]

Thanks bebo21999, not sure if I explained that part properly,. J38 is where the number of cells requiring input is compared to the number of cells with input. If the number is positive then there are cells requiring input and I wish users to resolve this before saving the file. If the answer is 0 (zero) then all is ok and the marco can progress to saving the file and e-mailing me a copy

 

[Chancer]

Fail results in blank cells next to the fail that require to be filled in, Pass and N/A do not, so when there are 2 fails in column F there will be 2 cells in each of G-I requiring input. If user does not enter info into G-I then the report is incomplete and I do not want them to be able to complete the form, save it or send it to me until resolved (save and send are automated via macro)

 

[Chancer]

As it stands this works once, but when user returns to input the missing info and activates the macro again (clicks on the assigned on screen button) to save/send nothing happens