List Box - Select an element?
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 |
List Box - Select an element?
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. --- Regards. Norman "NateBuckley" wrote in message ... 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 |
List Box - Select an element?
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. --- Regards. Norman |
All times are GMT +1. The time now is 02:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com