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

Solved/Closed
Raj_1562 Posts 31 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022 - Updated on Jul 28, 2021 at 06:50 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Aug 5, 2021 at 11:51 AM
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

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Jul 29, 2021 at 11:59 AM
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
Raj_1562 Posts 31 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022
Jul 30, 2021 at 01:27 AM
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Aug 2, 2021 at 12:06 PM
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
Raj_1562 Posts 31 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022
Aug 5, 2021 at 05:28 AM
Hi Trowa,

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

Regards,
Raj
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Aug 5, 2021 at 11:51 AM
Awesome, thanks for the feedback!
1