How to show a message box ?

[Rob]

Hi,
I would like a message box to display if the following conditions are met.
Assuming column A contains codes that do not change .

For example the code in column A6 is ADO and the value in G6 is less than
the value in H6 which is less than the value in K6. A message box comes up
with “Code ADO has reached criteriaâ€.

Being a complete newbie at VBA I would really appreciate some help.
Thankyou.

 

[Jacob Skaria]

Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and try out.

'If only in Row6
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A6,G6,H6,K6")) Is Nothing Then
If Range("A6") = "ADO" Then
If Range("G6") < Range("H6") And Range("H6") < Range("K6") Then
MsgBox "Code ADO has reached criteria"
End If
End If
End If
End Sub

'OR if you want this to work on all rows

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A" & Target.Row) = "ADO" Then
If Range("G" & Target.Row) < Range("H" & Target.Row) And _
Range("H" & Target.Row) < Range("K" & Target.Row) Then
MsgBox "Code ADO has reached criteria"
End If
End If
End Sub

 

[OssieMac]

Hi Rob,

The code that Jacob posted will run whenever you make any change anywhere on
the worksheet. This code is restricted to run only when a change is made in
one of columns G, H or K. Therefore select the option that you prefer.

Copy the code below and then right click on the worksheet tab and select
View Code. Paste the code into the VBA editor. Cick on the cross with the
red background top right of VBA editor to close the VBA editor. Save the
workbook. You will also need to ensure that macros are enabled in Macro
security. See help for how to do this. (If using xl2007 then save as Macro
Enabled workbook.)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngIsect As Range
Dim lngRow As Long

Set rngIsect = Application.Intersect(Target, _
Union(Columns("G"), Columns("H"), Columns("K")))

If Not rngIsect Is Nothing Then
lngRow = Target.Row

If Range("G" & lngRow) < Range("H" & lngRow) And _
Range("H" & lngRow) < Range("K" & lngRow) Then

MsgBox "Code ADO has reached criteria"
End If

End If

End Sub

 

[Jacob Skaria]

You can also amend the 1st option as below...

If Not Application.Intersect(Target, Range("A:A,G:G,H:H,K:K")) Is Nothing Then
If Range("A" & Target.Row) = "ADO" Then
If Range("G" & Target.Row) < Range("H" & Target.Row) And _
Range("H" & Target.Row) < Range("K" & Target.Row) Then
MsgBox "Code ADO has reached criteria"
End If
End If
End If

 

[Rob]

Thanks for your help Jacob.

Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and try out.

'If only in Row6
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A6,G6,H6,K6")) Is Nothing Then
If Range("A6") = "ADO" Then
If Range("G6") < Range("H6") And Range("H6") < Range("K6") Then
MsgBox "Code ADO has reached criteria"
End If
End If
End If
End Sub

'OR if you want this to work on all rows

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A" & Target.Row) = "ADO" Then
If Range("G" & Target.Row) < Range("H" & Target.Row) And _
Range("H" & Target.Row) < Range("K" & Target.Row) Then
MsgBox "Code ADO has reached criteria"
End If
End If
End Sub

 

[Rob]

Thanks for your help OssieMac.