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

Solved
Raj_1562
Posts
31
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
August 5, 2021
- Updated on Jul 28, 2021 at 06:50 AM
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
- 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
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
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 5, 2021

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
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
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 5, 2021

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
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Aug 5, 2021 at 11:51 AM
Awesome, thanks for the feedback!
0