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

[Solved]
Report
Posts
31
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
August 5, 2021
-
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
-
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.

3 replies

Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
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
1
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2821 users have said thank you to us this month

Posts
31
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
August 5, 2021

Hi TrowaD,

Thanks for the update!

Hope you are doing well.

I have tried the above code. But there is no response on the excel file.

I have uploaded the Sample file with detailed notes. Can you please check the below link.

https://easyupload.io/4lrof0

Thanks in advance!

Regards,
Raj
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
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
1
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2821 users have said thank you to us this month

Posts
31
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
August 5, 2021

Hi Trowa,

I had tested the file. It's working perfectly as expected. I appreciate your hard work.

Regards,
Raj
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
Awesome, thanks for the feedback!