Specify Rows and Cols for worksheet Change Event?

  • Thread starter Thread starter Rich
  • Start date Start date
R

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.)
 
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
 
Gord,
Thanks, it works great.

Rich

Gord Dibben said:
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
Click to expand...
 
Back
Top