Event codes with two conditions

rhmkrmi

Board Regular
Joined
Aug 17, 2012
Messages
191
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
191
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
191
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,082,243
Messages
5,363,972
Members
400,772
Latest member
solbebe

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top