How can I show an error message through data validation when the cell is auto populated

[kjohn25744]

I am building an order form, and I am having problems displaying an error message when a value in a cell is auto populated. (See diagram below) I have created a data validation on the list price, but I cannot get it to trigger when the field is populated via a VLOOKUP formula. I need an error message to be displayed so the customer knows that item is not available and to choose another item. (Note some items are available in a different color.

 

[Micron]

If you don't mind using code, you should be able to use the worksheet calculate event. So user makes a selection and you check if the offset of 4 columns from the active cell contains an X and then open a message box? Or does the user do something in between the validation selection and the calculation that results in X?

 

[kjohn25744]

The customer will enter the cabinet name then formals calculate the item code and the list price. So yes, I think your solution could work, but I know just enough VBA to be dangerous. The Item Name will be in column C, the item code is in column F and the List Price is in column G.

 

[Micron]

So if it works like this
- I use drop-down validation, formula recalculates immediately
then it should work. I think the issue would be that this would not force the user to remove the item or make a change. Code can delete the item from the cell but the probability that the calculate event will run again has to be dealt with. Hopefully I took care of that.

Open the vb editor (alt+F11) double click on the proper sheet from the objects list.

Choose Worksheet from the top left menu

Choose Calculate from the right menu. Paste this in between the Sub / End Sub lines:

If UCase(ActiveCell.Offset(0, 4)) = "X" Then
    MsgBox "Sorry, this item is not currently available." & vbCrLf & "Please choose a different item."
    Application.EnableEvents = False
    ActiveCell = ""
    Application.EnableEvents = True
End If

See if that works for you. I suggest you do this in a copy of your workbook to test first.
You could put your message(s) in a helper sheet and pull them from there, otherwise you'll have to edit the message in the code itself.

EDIT - I corrected offset to 4.

 

[kjohn25744]

Thanks that is working like I want it to work. Thanks so much, I was pulling out my hair trying to figure out a way using data validation.

 

[Micron]

Glad to help & thanks for the recognition.

 

[kjohn25744]

Micron,
The code you gave me works great, however I have multiple sheets in my workbook and it is executing on all the sheets even though I double clicked on the sheet I want it to work for and entered the VBA code there. But now when I am on another sheet in the same workbook, it is executing on that sheet as well. I added another couple of lines in your code, but I do not think that is causing the problem. Here is the code I entered. Any suggestions?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
--------------------------------------------------------------------------------------------------------------------------------------------------------------------

Private Sub Worksheet_Calculate()
If UCase(ActiveCell.Offset(0, 7)) = "X" Then
MsgBox "Sorry, this item is currently unavailable in this style and color." & vbCrLf & "Please make a different selection."
Application.EnableEvents = False
ActiveCell = ""
ActiveCell.Offset(0, 1) = ""
Application.EnableEvents = True
End If
End Sub

 

[Micron]

The only way I can see it acting on all sheets is if you pasted the code in all the affected sheets or used the event at the workbook level (SheetCalculate). Based in what you posted neither of those things appear to be the case. Try removing your edit(s) and see what happens. Perhaps this
ActiveCell.Offset(0, 1) = ""

is triggering other code to run? Or are you saying that on the other affected sheets, the exact same changes are being made? You could first try putting a break point at the 1st code line and step through (F8) and watch what happens.

Did you get my PM?