Apr 24 2022 11:12 AM
Hi all.
I need some brains here.
I need a VBA code for this action:
I want a simple message box to appear (a warning) depending on the choices made by the person who uses this worksheet.
For example: when the result in cell A1 = "Boston" OR "Antwerp" OR "Berlin" AND the result in B1= "red" OR "blue" OR "green" AND the result in C1= "Yes" then i need a message box (warning) that says : "your choice is not possible!"
Of course this must work also for all the rows in this worksheet...
For me too complicated. I understand the VBA code but writing it is not my cup of tea!
Can someone help me with this?
Greeny95
Apr 24 2022 11:30 AM
No VBA needed.
Select columns A to C. The active cell in the selection should be in row 1.
On the Data tab of the ribbon, click Data Validation.
Select Custom from the Allow drop-down.
Enter the following in the Formula box:
=NOT(AND(OR($A1="Boston",$A1="Antwerp",$A1="Berlin"),OR($B1="red",$B1="blue",$B1="green"),$C1="Yes"))
Activate the Error Alert tab.
Enter an appropriate message.
Click OK.
Apr 25 2022 01:08 PM
Hi Hans (per toeval Nederlands-sprekend?)
Thanks for your answer!
I tried your solution. And it worked but: in one of the columns I use already data validation with a list. When I use your solution I can't use the dropdown menu anymore in that column.
That's why I think I need a macro. Unless there is another solution?
Kind regads
Manu (Greeny95)
Apr 25 2022 01:53 PM
Apr 25 2022 02:24 PM
You are correct - data validation of type Custom cannot be combined with data validation of type List.
Here is a VBA-based idea.
Apr 28 2022 01:00 PM
Apr 28 2022 01:25 PM
There can only be one Worksheet_Change event procedure per worksheet. so you have to copy the code of one of the procedures (without the lines Private Sub ... and End Sub) into the other one, then remove the first one entirely.
Apr 28 2022 01:28 PM
Use
=NOT(AND(OR($A1="Boston",$A1="Antwerp",$A1="Berlin"),OR($B1="red",$B1="blue",$B1="green"),$C1="Yes",WEEKDAY($D1)=1)
In Dutch:
=NIET(EN(OF($A1="Boston";$A1="Antwerp";$A1="Berlin");OF($B1="red";$B1="blue";$B1="green");$C1="Yes";WEEKDAG($D1)=1)
Apr 29 2022 01:36 PM
Using the same example as before:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rw As Range
Dim r As Long
If Not Intersect(Range("A2:D20"), Target) Is Nothing Then
For Each rw In Intersect(Range("A2:D20"), Target).Rows
r = rw.Row
If (Range("A" & r).Value = "Antwerp" Or Range("A" & r).Value = "Berlin" Or Range("A" & r).Value = "Boston") And _
(Range("B" & r).Value = "blue" Or Range("B" & r).Value = "green" Or Range("B" & r).Value = "red") And _
Range("C" & r).Value = "Yes" And Weekday(Range("D" & r)) = vbSunday Then
MsgBox "Incorrect selection in row " & rw.Row, vbExclamation
End If
Next rw
End If
End Sub