Forum Discussion

Copper Contributor

Apr 24, 2022

VBA code message box

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 exam...

excel

Macros and VBA

Greeny95

Copper Contributor

Apr 28, 2022

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

HansVogelaar

MVP

Apr 28, 2022

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)

Greeny95

Copper Contributor

Apr 29, 2022

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

HansVogelaar

MVP

Apr 29, 2022

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

Greeny95
Copper Contributor
Apr 29, 2022
Thanks a lot, Hans! It works perfect!!!!