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]

Give this code a try. Put the following code under ThisWorkbook in the VBAProject window
Thanks to MrKowz for help on this issue

[/COLOR]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"
End If


'   Check if C = Yes and there are blank cells in Columns E:H
blankcells = 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"
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"
End If


End Sub


[COLOR=#333333]

 

[vimal606]

Thanks a lot MrKowz.... Its working fine.
But want the macro to start when the user attempts to save the excel sheet

 

[Joe4]

But want the macro to start when the user attempts to save the excel sheet

It will, if you put it in the right place, it will run automatically whenever anyone tries to save the file.
Notice what Frank said:

Put the following code under ThisWorkbook in the VBAProject window

You MUST put it in the "ThisWorkbook" module in the VB Editor. If you put it in any other module, it will not work autamatically.

 

[vimal606]

I am Sorry MrKowz.. i figured out to trigger the macro on save.
What i require is without the conditions in the messages are satisfied the sheet should not be saved.

Is there a way to do it

 

[Joe4]

There is no "MrKowz" who has posted to this thread! Frank was just telling you that he borrowed the code from a post from MrKowz.

If you want to cancel the save, you simply add the line:

Cancel = True

where appropriate.

See here for explanations and examples: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/workbook-beforesave-event-excel

 

[vimal606]

Thank you.... You are a lifesaver

 

[vimal606]

One more clarification please... when i use the below macro, if i enter data in any one cell from E to H, the message box is not coming up for that particular row.


' Check if C = Yes and there are blank cells in Columns E:H
blankcells = 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"
End If

 

[Joe4]

The way you have written the code, it is only checking for the situation where column C has "Yes", and ALL columns E:H are blank.

 

[vimal606]

Can you help me set the macro as where column C has "Yes", and any column in E:H are blank.