List Box - Select an element?

[NateBuckley]

Hello I'm just wondering if it's possible to get the program to select a
value in a list box,

I have a lot of different codes all within a list box, and sometimes it's
quite hard to find a particular one. I'm just wondering if it'd be possible
to let the user type in a code and then this automatically highlight the
found code within the list box?

Thanks for any help in advance!

Nathan

 

[Norman Jones]

Hi Nate,

Put a ListB ox from the Controls Toolbox
on a worksheet.

In a standard module (see below), paste
the following code:

'==========>>
Option Explicit

Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim arr As Variant
Dim LBox As OLEObject

Set WB = ThisWorkbook
Set SH = WB.Sheets("Sheet1") '<<====CHANGE
Set LBox = SH.OLEObjects("Listbox1") '<<====CHANGE

arr = VBA.Array("Blue", "Green", _
"Yellow", _
"Brown", _
"Grey", _
"Lilac", _
"Pink") '<<====CHANGE

Set LBox = SH.OLEObjects("Listbox1")

With LBox.Object
.Clear
.List = arr
End With

End Sub
'<<==========

Alt-F11 to open the VBA Editor
Menu | Insert | Module
Paste the above code
Alt-F11 To return to Excel

In the worksheet module (see below),
paste the following code:

'==========>>
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim oleObj As OLEObject
Dim i As Long

Set oleObj = Me.OLEObjects("Listbox1") '<<====CHANGE
Set Rng = Me.Range("A1") '<<==== CHANGE

Set Rng = Intersect(Rng, Target)

If Not Rng Is Nothing Then
With oleObj.Object

For i = 0 To .ListCount - 1
If StrComp(.List(i), Rng.Value, _
vbTextCompare) = 0 Then
.ListIndex = (i)
Exit For
End If
Next i
End With
End If
End Sub
'<<==========

The above code is worksheet event code
and should be pasted in the module behind
the worksheet:

Right-Click the worksheet tab
Select 'View Code'
Paste

Now, entering any of the ListBox values in
cell A1, on the sheet of interest, will select
that value in the ListBox.

 

[Norman Jones]

Hi Nate,

Alternatively, and perhaps more simply:

- replace the ListBox with a ComboBox
from the Control Toolbox

- dispense with the code in the worksheet
module

- replace the code suggested for the
standard module with the folloing version

Now, using the ComboBox's MatchEntry
property, as the user types each letter, the
control will sugggest a value.