Popup message when mandatory columns are not filled in a row in ExcelSolved/Closed

Question

Hi All,
Hope you are doing good!

I want to popup an error message before saving. The requirements are
for eg:- In this table, "Name & PhnNo" are mandatory columns.
1.1st row is fine,
2. 2ND row, phn no is not filled, so it should show a popup message
3. 3rd row also Name is empty, so popup needed
4.4th row, fields are missing but they are not mandatory, so we can ignore it
this excel's rows keep on increasing, Every user will be adding one new row and a message has to be displayed before save.
Kindly let me know if any more clarification is needed.
Thanks in advance.

TrowaD · Answer

Hi Raj,

Something like this?:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim lRow As Integer

lRow = Range("D" & Rows.Count).End(xlUp).Row

For Each cell In Range("A2:A" & lRow)
    If cell.Value = vbNullString Then MsgBox "The following cell in mandatory:" & vbNewLine & "A" & cell.Row, vbCritical, "Missing data"
    Cancel = True
Next cell

For Each cell In Range("C2:C" & lRow)
    If cell.Value = vbNullString Then MsgBox "The following cell in mandatory:" & vbNewLine & "C" & cell.Row, vbCritical, "Missing data"
    Cancel = True
Next cell
End Sub

 The code uses column D to determine the last row.
 When an empty cell is found in columns A or C, then the file is NOT saved.

Let us know if alterations are desired.

Best regards,
Trowa

TrowaD · Answer

Hi Raj,

I'm doing great! Hopefully you are as well.

Your sample file shows a different setup. You also placed the code in sheet1 instead of ThisWorkbook

Good to know that column A is always filled, so we can use that to determine the size of your table.
Also good to know you are using a table setup, so we have to determine the last row by going from top to bottom.

I've adjusted the code and placed it under ThisWorkbook.

Here is the code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim lRow As Integer

lRow = Range("A1").End(xlDown).Row

For Each cell In Range("C2:C" & lRow)
    If cell.Value = vbNullString Then
        MsgBox "The following cell in mandatory:" & vbNewLine & "C" & cell.Row, vbCritical, "Missing data"
        Cancel = True
    End If
Next cell
If Cancel = True Then MsgBox "The file is NOT saved!", vbCritical
End Sub

When you click the save icon, you should get 3 message boxes. One for C3 and one for C5.
When you click the red cross (in the top right) to close the file and you made a change in the file, you will also get the question to save the file. When you click 'Save', the file closes, giving the impression that the file got saved. So I added another message box to let the user know that the file did not get saved.

Here is your file:
https://easyupload.io/37vyyh

Best regards,
Trowa

TrowaD · Answer

Awesome, thanks for the feedback!

Popup message when mandatory columns are not filled in a row in Excel

3 replies

Subscribe To Our Newsletter!