Related:
- Popup message when mandatory columns are not filled in a row in Excel
- Send popup message to another computer on network - How-To - Windows XP
- Send popup message to another computer on network windows 10 ✓ - Forum - Network
- Windows login popup message - How-To - Windows 7
- HOW TO SEND MESSAGE ON LAN ✓ - Forum - Network
- Excel reminder popup message - Forum - Excel
3 replies
TrowaD
- Posts
- 2817
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- October 14, 2021
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
- 2817
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- October 14, 2021
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
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