• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Simple error message if cell is blank and worksheet is saved

Abbas

Abbas

New Member
Hi,


I am making a sheet where there are 3 columns. Column A has 3 list items Contract, Under-warranty and Out-of-Waranty. Column B is for warranty certificate no. and Column C for expiry date of warranty.

If a person selects "Under-warranty" in A2 from the list, but does not enter a certificate number in B2 and/or expiry date in C2, and saves the workbook, I want an error message to display a message "You need to enter a value in B2 and/or C2"


Similarly, if a person selects "Under-warranty" in A65 from the list, but does not enter a certificate number in B65 and/or expiry date in C65, and saves the workbook, I want an error message to display a message "You need to enter a value in B65 and/or C65"


However, if the other two list items "Contract" or "Out-of-Warranty" is selected and the workbook is saved, there should not be any message...
 
I'd setup a cell somwhere with this formula to validate that all entries have been completed.

=COUNTIF(A:A,"under-warranty")=SUMPRODUCT((A2:A1000="under-warranty")*((B2:B1000<>"")+(C2:C1000<>"")>0))


Then, in the VBE, in the ThisWorkbook, module, put something like this:

[pre]
Code: 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Worksheets("Sheet1").Range("CheckCell").Value = False Then
MsgBox "Please fill in data for Under-Warranty items"
Cancel = True
End If

End Sub
[/pre]
If you need to call attention to which cell, I'd suggest just using some Conditional Formatting. That way users can see what needs to be filled in as they are working initially.
 
Hi Abbas..


Just trying to follow masters route..

[pre]
Code: 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If [=SUMPRODUCT((A:A="Under-Warranty")*(B:B<>"")*(C:C<>""))] < [=COUNTIF(A:A,"Under-Warranty")] Then
MsgBox "Error Message"
Cancel = True
End If
End Sub
[/pre]
@ Luke..

Can you please help me figure out Incomplete Row's Number and fit the same in message.. :)

BTW.. are you using laptop !!! your "=" and "-" both key are overlapping.. :)


Regards,

Deb
 
It works but with some error... The cell I setup shows true when nothing or contract or out-of-warranty is selected in drop-down and false when under-warranty is selected but nothing is entered in other two columns. So good so far. But I added the above script in VBA and changed the name of Sheet1 to Warranty Sheet. But when I save the script I get the error "Run-time error '1004': Application-defined or object-defined error". It yellow highlights the second line of script.


The script does work though and I got the message as intended.


I would also like to give the option 'Yes' to continue saving and 'No' to return for entering the value.
 
Hi Abbas,


DO NOT TRY THIS CODE ON YOUR SHEET.


I am still learning VBA and came up with this. This gives the required result to an extent but looks like I have to click at leat 4-5 times on YES or NO button to get rid of the message box.

Also, even when I select YES, it doennt seem to save the workbook.


Any suggestions, ideas would be welcome.

[pre]
Code: 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim i As Long

For i = 2 To 100
If Cells(i, 1).Value = "Under Warranty" And (Cells(i, 1).Offset(0, 1).Value = "" Or _
Cells(i, 1).Offset(0, 2).Value = "") Then
MsgBox "Please fill in data for Certificate number and Expiry date.", vbYesNo
If vbNo Then Cells(i, 1).Select
Exit Sub
Else: ThisWorkbook.Save
Cancel = True
End If
Next i

End Sub
[/pre]
Jai
 
Hi Debraj,


your script executes beautifully and I am happy with it. But it doesn't give me the option to continue saving if i have intentionally left the cells blank. Pls help.
 
Hi Abbas,


Try This..

[pre]
Code: 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If [=SUMPRODUCT((A:A="Under-Warranty")*(B:B<>"")*(C:C<>""))] < [=COUNTIF(A:A,"Under-Warranty")] Then
If MsgBox("Error Message" & Chr(10) & "Do you still want to save!!", vbYesNo) = vbNo Then Cancel = True
End If
End Sub
[/pre]
Regards,

Deb
 
@Debraj,


Not sure about picking specific row that has error. Note that there is a slight difference between our two formulas. OP originally stated that there needed to be a value in col B or col C. Because of the Or logic, needed to add those 2 arrays. Your formula works on an And type logic. Now, OP says that it's working, so maybe this is not really an issue.


I'm guessing this is the point you were making about my keys? =P
 
Hi Luke..

I guess.. below Second =(Equal) may be need to change to -(minus), but I still need to learn a lot in BINARY logic.. sometime this type of comparison operator works great.. Not sure !!!

=COUNTIF(A:A,"under-warranty")=SUMPRODUCT((A2:A1000="under-warranty")*((B2:B1000<>"")+(C2:C1000<>"")>0))


BTW regarding AND OR part.. our thinking is slightly different.. where you searching for If any of the parallel Column is not filled.. and I am searching for if Both column's are not filled.. :)


Regards,

Deb
 
@Debraj,


Ah, you're correct, I missed that you were checking for not filled. Which is probably a better way to go. =)
 
You must log in or register to reply here.
Back
Top