Specify Rows and Cols for worksheet Change Event?

[Rich]

Hi,
Can someone please tell me how to change the below Visual Basic code
(originally from Nick H.) for excel so that the Target area it applies to
is not columns A:A but applies only to Columns J thru M and Rows 25 thru 36.


ORIGNAL REPLY:

You could use a worksheet change event. The code below will present a
message box on each entry. Test it with validation first. As written it

only works with entries in column A (The code goes behind a worksheet in the

workbook. Right click on a sheet tab and select 'View code'. Paste the code

in the resultant window)



Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Columns("A:A")) Is Nothing Then

Select Case Target.Value

Case Is = "Crayon"

MsgBox "Select colour later", vbOKOnly, "Crayons"

Case Is = "Kite"

MsgBox "Select shape later", vbOKOnly, "Kites"

Case Is = "Fish"

MsgBox "Select species later", vbOKOnly, "Fishes"

Case Is = "Box"

MsgBox "Select size later", vbOKOnly, "Boxes"

Case Else

Exit Sub

End Select

End If

End Sub

--

Thanks,

rick

(e-mail address removed) (<= remove "Z"s for valid email.)

 

[Gord Dibben]

Rich

Option Compare Text 'added so case-insensitive
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("J25:M36")) Is Nothing Then
Select Case Target.Value
Case Is = "Crayon"
MsgBox "Select colour later", vbOKOnly, "Crayons"
Case Is = "Kite"
MsgBox "Select shape later", vbOKOnly, "Kites"
Case Is = "Fish"
MsgBox "Select species later", vbOKOnly, "Fishes"
Case Is = "Box"
MsgBox "Select size later", vbOKOnly, "Boxes"
Case Else
Exit Sub
End Select
End If
End Sub

Gord Dibben Excel MVP

 

[Rich]

Gord,
Thanks, it works great.

Rich

Rich

Option Compare Text 'added so case-insensitive
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("J25:M36")) Is Nothing Then
Select Case Target.Value
Case Is = "Crayon"
MsgBox "Select colour later", vbOKOnly, "Crayons"
Case Is = "Kite"
MsgBox "Select shape later", vbOKOnly, "Kites"
Case Is = "Fish"
MsgBox "Select species later", vbOKOnly, "Fishes"
Case Is = "Box"
MsgBox "Select size later", vbOKOnly, "Boxes"
Case Else
Exit Sub
End Select
End If
End Sub

Gord Dibben Excel MVP