If cells are empty do not allow save

[vimal606]

Dear Team,

I have an excel sheet which is filled by line managers. Very often i get back incomplete sheets.

1) I have drop down values enabled in Column "C". If drop down value is selected as "YES", then columns "E" to "H" are mandatory. User should not be able to save the sheet without filling in all the columns. A warning message saying "Please input values in required columns"

2) Colum "D" is also a drop down. If "D" is selected as Yes, there should be a warning message "Please enter training details in Column "G".
The message can be displayed on closing. User can save the excel without data input

3) Maximum allowed "YES" in Column "C" is 10. if the count exceeds 10, a warning message should be displayed as "Exceeds quota"

It would be of great help if you can solve this.

 

[frank_AL]

I'll make an adjustment to the code and get back to you.

 

[Joe4]

You can do that by counting the number of cells in column C that are equal to "Yes", and then subtracting the number of them column C is "Yes", and all the other cells are populated in columns E-H.
That blankCells calculation would look like:

    blankCells = Application.WorksheetFunction.CountIf(Range("C:C"), "Yes") - _
        Application.WorksheetFunction.CountIfs(Range("C:C"), "Yes", Range("E:E"), "<>" & "", Range("F:F"), "<>" & "", Range("G:G"), "<>" & "", Range("H:H"), "<>" & "")

 

[vimal606]

Perfect

 

[frank_AL]

Vimal, I have updated the code using Joe's suggestions to correctly determine rows that don't have entries in columns E:H and added the Cancel = True so that the file isn't saved unless all conditions are properly met.
Thanks Joe for your help.

Option Explicit


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean)


Dim i As Long
Dim lr As Long
Dim blankcells As Integer


'   Check to see if there are more than 10 rows in Column C with "Yes"
blankcells = Application.WorksheetFunction.CountIfs(Range("C:C"), "Yes")
If blankcells > 10 Then
    MsgBox "Exceeds Quota"
    cancel = True
End If


'   Check if C = Yes and there are blank cells in Columns E:H
blankcells = Application.WorksheetFunction.CountIf(Range("C:C"), "Yes") - _
        Application.WorksheetFunction.CountIfs(Range("C:C"), "Yes", Range("E:E"), "<>" & "", Range("F:F"), "<>" & "", Range("G:G"), "<>" & "", Range("H:H"), "<>" & "")
If blankcells > 0 Then
    MsgBox "You have entered 'Yes' in Column C" _
    & vbCrLf & "but have left blank cell(s) in " & blankcells & " Rows in  Columns E through H"
    cancel = True
End If


'   Check if D = Yes and there are blank cells in Columns E:H
blankcells = Application.WorksheetFunction.CountIfs(Range("D:D"), "Yes", Range("G:G"), "")
If blankcells > 0 Then
    MsgBox "You have entered 'Yes' in Column D" _
    & vbCrLf & "but have left 'Training Details' blank in  Columns G"
    cancel = True
End If


End Sub