Popup message when mandatory columns are not filled in a row in Excel
Solved/Closed
Raj_1562
Posts
29
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 December 27, 2022 - Aug 5, 2021 at 11:51 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Aug 5, 2021 at 11:51 AM
Related:
- Popup message when mandatory columns are not filled in a row in Excel
- Voice message downloader - Guide
- Send popup message to another computer on network windows 10 - Guide
- Number to words in excel - Guide
- Gif in excel - Guide
- How to pin a message in messenger - Guide
3 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Jul 29, 2021 at 11:59 AM
Jul 29, 2021 at 11:59 AM
Hi Raj,
Something like this?:
Let us know if alterations are desired.
Best regards,
Trowa
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
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Aug 2, 2021 at 12:06 PM
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:
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
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
Raj_1562
Posts
29
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
August 29, 2022
Aug 5, 2021 at 05:28 AM
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
I had tested the file. It's working perfectly as expected. I appreciate your hard work.
Regards,
Raj
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Aug 5, 2021 at 11:51 AM
Aug 5, 2021 at 11:51 AM
Awesome, thanks for the feedback!
Jul 30, 2021 at 01:27 AM
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