VBA code message box

[Greeny95]

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

[#2]

@Greeny95 

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.

[#3]

@Hans Vogelaar 

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)

[#4]

@Hans Vogelaar: I forgot to mention this: as it happens that a person made the wrong choice, the 3 cells (A1,B1 and C1) get a red colour. (with conditional formatting) Is that something that can trigger a message box?

[#5]

@Greeny95 

You are correct - data validation of type Custom cannot be combined with data validation of type List.

Here is a VBA-based idea.

[#6]

@Greeny95 

Ik ben Nederlands, trouwens...

[#7]

Hi Hans,
I found time again to work on my excel. Busy times...
Your VBA code works perfect. Wauw! Thanks a lot. I've two more little questions. I run also another VBA code in the same sheet. I copied your code on the same page. Now I get the message: compileerfout: onduidelijke naam gedetectreerd: Worksheet_Change. The two codes start with the same name. I tried to change one of them by adding a "2" but then, the code with the changed name don't work anymore.
And another small thing: I like to add one more "and" condition. Row D are dates. So when the 3 previous conditions are true AND it's a sunday then I need the warning to pop up. I hoped it was something like 'WEEKDAG("D")=1 ' but it wasn't that easy. I hope you can help me one more time! Thanks. Manu

[#8]

type MsgBox "the massage", vbOKOnly , "subject"

[#9]

@Greeny95 

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.

[#10]

@Greeny95 

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)

[#11]

Ah ok!

[#12]

I knew the formula. But I meant the VBA code...

[#13]

@Greeny95 

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

[#14]

Thanks a lot, Hans! It works perfect!!!!