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
Multiple code for Worksheet_Change event
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: vba excel Multi Select List Box
1. There can be only one Worksheet_Change event for a particular worksheet, but you can check multiple ranges:
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.
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
Or use the KeyPress event to react to each key press by the user.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 12650
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: vba excel Multi Select List Box
You do this by using a single Worksheet_Change event and taking different action depending on which range includes the changed cell(s).MSingh wrote:...
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?
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
StuartR
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: vba excel Multi Select List Box
Hi Hans,
Thanks again for the code - it works perfectly.
I also Thank Stuart for repsonding-many thanks.
Kind Regards
Mohamed
Thanks again for the code - it works perfectly.
I also Thank Stuart for repsonding-many thanks.
Kind Regards
Mohamed