Multiple code for Worksheet_Change event

[MSingh]

Hi Hans,

I have more questions:

1. Is is possible to have more than 1 (say2) worksheet change events :Private Sub Worksheet_Change(ByVal Target As Range) for 2 different ranges in the same worksheet?
2. Is there a "type ahead" feature for a List Box aside from changing the "matchentry" property to "firstletter" or "Complete"? Ideally, something that works like a windows search ie. when you type it start to autocomplete the word?

Thanking you for your assistances
Mohamed

[HansV]

1. There can be only one Worksheet_Change event for a particular worksheet, but you can check multiple ranges:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim oCell As Range

  If Not Intersect(Range("A1:A10"), Target) Is Nothing Then
    For Each oCell In Intersect(Range("A1:A10"), Target)
      ' code to handle changed cell in range A1:A10 goes here
      ...
    Next oCell
  End If

  If Not Intersect(Range("G4:L4"), Target) Is Nothing Then
    For Each oCell In Intersect(Range("G4:L4"), Target)
      ' code to handle changed cell in range G4:L4 goes here
      ...
    Next oCell
  End If
End Sub

2. You could set MatchEntry to Complete, and use the Change event of the list box. It will fire each time a different entry is selected as the user types.
Or use the KeyPress event to react to each key press by the user.

[StuartR]

...
1. Is is possible to have more than 1 (say2) worksheet change events :Private Sub Worksheet_Change(ByVal Target As Range) for 2 different ranges in the same worksheet?

You do this by using a single Worksheet_Change event and taking different action depending on which range includes the changed cell(s).

Code: Select all

If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
	Call sub1(Target)
End If

If Not Intersect(Target, Range("D1000:E5000")) Is Nothing Then
	Call sub2(Target)
End If

[MSingh]

Hi Hans,

Thanks again for the code - it works perfectly.

I also Thank Stuart for repsonding-many thanks.
Kind Regards
Mohamed