Popup message when mandatory columns are not filled in a row in Excel
Solved
Raj_1562
TrowaD
- Posts
- 31
- Registration date
- Wednesday August 26, 2020
- Status
- Member
- Last seen
- August 5, 2021
TrowaD
- Posts
- 2880
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- May 2, 2022
Related:
- Popup message when mandatory columns are not filled in a row in Excel
- Send popup message to another computer on network - Guide
- Windows login popup message - Guide
- Send popup message to another computer on network windows 10 ✓ - Forum - Network
- Excel reminder popup message - Forum - Excel
- Uneditable selected Columns/Rows/Cells Excel ✓ - Forum - Excel
3 replies
TrowaD
Jul 29, 2021 at 11:59 AM
- Posts
- 2880
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- May 2, 2022
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
Aug 2, 2021 at 12:06 PM
- Posts
- 2880
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- May 2, 2022
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
Aug 5, 2021 at 05:28 AM
- 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
I had tested the file. It's working perfectly as expected. I appreciate your hard work.
Regards,
Raj
TrowaD
Aug 5, 2021 at 11:51 AM
- Posts
- 2880
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- May 2, 2022
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