If cells are empty do not allow save

vimal606

New Member
Joined
Nov 21, 2017
Messages
12
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Give this code a try. Put the following code under ThisWorkbook in the VBAProject window
Thanks to MrKowz for help on this issue

Code:
[/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]

 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top