Event codes with two conditions

rhmkrmi

Board Regular
Joined
Aug 17, 2012
Messages
217
Hi, Can you please help me get an event code with two conditions to generate a message for two scenarios as follows?

1)
I am trying to translate this formula into VBA:
IF(AND($A1="Home",$B1="School"),"Invalid Combination","")
I want the event code to check these conditions for rows 1 to 20.
I want the message to pop up only and as soon as these conditions are met on any row in range A1 to B20.

2)
I need an event code with a message box for this formula:
IF(AND(ISNUMBER($A1),ISBLANK($Z1)),"Parameter Missing","")
I want the event code to check these conditions for rows 1 to 20.
I want the message to pop up only if these conditions are met and at the time the user leaves the sheet not as soon as the conditions are met.

Thank you.
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hi,

Event macros are triggered ... for example ... by a cell change ...

Which are the ' source ' cells which will trigger your macro ...?
 

rhmkrmi

Board Regular
Joined
Aug 17, 2012
Messages
217
In the first scenario, the cells that trigger the macro are A1:A20 and B1:B20.

In the second scenario, they are A1:A20 and Z1:Z20.

Thank you.
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hi again,

You can following macro for your first scenario

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then ExitSub
If Intersect(Target, Range("A1:B20")) Is Nothing Then Exit Sub
    Select Case Target.Column
        Case 1
            If Target = "Home" And Target.Offsetset(0, 1) = "School" Then
                MsgBox " Invalid Combination "
            End If
        Case 2
            If Target = "School" And Target.Offsetset(0, -1) = "Home" Then
                MsgBox " Invalid Combination "
            End If
    End Select
End Sub
Hope this will help
 

rhmkrmi

Board Regular
Joined
Aug 17, 2012
Messages
217
Thank you very much.

I have difficulty combining it with the below which is some existing codes on the same sheet. Where and how do I add your code to test it?

Private Sub Worksheet_Change(ByVal Target As Range)


' ***BLOCK1***
Dim sUndoList As String


On Error Resume Next


If Not Intersect(Target, Range("A1:Z100")) Is Nothing Then
sUndoList = CommandBars.FindControl(ID:=128).List(1)
If Left(sUndoList, 5) = "Paste" Or sUndoList = "Auto Fill" Or sUndoList = "Drag and Drop" Then
Application.EnableEvents = False
Application.Undo
Application.OnUndo "", ""
Application.EnableEvents = True
End If
End If



' ***BLOCK2***
Dim rng As Range
Dim cell As Range
Dim rw As Long


' See if any cells updated in column B
Set rng = Intersect(Target, Range("B:B"))
If rng Is Nothing Then Exit Sub


Application.EnableEvents = False


' Loop through updated cells in column B
For Each cell In rng
rw = cell.Row
Select Case cell.Value
Case "Home"
Range(Cells(rw, "F"), Cells(rw, "S")) = "N/A"
Range(Cells(rw, "E"), Cells(rw, "E")) = ""
Range(Cells(rw, "F"), Cells(rw, "S")).Interior.Color = 15132390
Range(Cells(rw, "E"), Cells(rw, "E")).Interior.Pattern = xlNone
Case "School"
Cells(rw, "E") = "N/A"
Range(Cells(rw, "F"), Cells(rw, "S")) = ""
Cells(rw, "E").Interior.Color = 15132390
Range(Cells(rw, "F"), Cells(rw, "S")).Interior.Pattern = xlNone
Case Else
Range(Cells(rw, "E"), Cells(rw, "S")) = ""
Range(Cells(rw, "E"), Cells(rw, "S")).Interior.Pattern = xlNone
End Select
Next cell

Application.EnableEvents = True


End Sub


Private Sub Worksheet_Activate()
MsgBox "Check your selections."
End Sub
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
May be

Code:
' Loop through updated cells in column B
    For Each cell In Rng
        rw = cell.Row
        Select Case cell.Value
            Case "Home"
                Range(Cells(rw, "F"), Cells(rw, "S")) = "N/A"
                Range(Cells(rw, "E"), Cells(rw, "E")) = ""
                Range(Cells(rw, "F"), Cells(rw, "S")).Interior.Color = 15132390
                Range(Cells(rw, "E"), Cells(rw, "E")).Interior.Pattern = xlNone
                
            Case "School"
                Cells(rw, "E") = "N/A"
                Range(Cells(rw, "F"), Cells(rw, "S")) = ""
                Cells(rw, "E").Interior.Color = 15132390
                Range(Cells(rw, "F"), Cells(rw, "S")).Interior.Pattern = xlNone
                ' Added Test ''''''''''''''''''''''''''''''''''''''''''''''''''
                If cell.Offset(0, -1) = "Home" Then MsgBox "Invalid Combination"
            Case Else
                Range(Cells(rw, "E"), Cells(rw, "S")) = ""
                Range(Cells(rw, "E"), Cells(rw, "S")).Interior.Pattern = xlNone
        End Select
    Next cell
Hope this will help
 

Forum statistics

Threads
1,085,294
Messages
5,382,768
Members
401,804
Latest member
RB85

Some videos you may like

This Week's Hot Topics

Top