Excel VBA: Listbox

Question

on a separate Excel 2007 VBA question, how do i use the listbox under "Developer" > "Form Control" ?

i would like to display some data from another worksheet (say wsData), however i would like to display the data specific to a user (say wsInterface, cell A1), and NOT use the "format control" > "control" > "input range"

so i will have a form that pops up to prompt the user for a name to be keyed into worksheet wsInterface cell A1, and i would like the data relevant to the user to be displayed in the listbox. Thanks

wsData:

name  |   date   | remarks
Mr A     22/2/11   blah blah blah 1.
Mr B     22/2/11   blah blah blah 2.
Mr A     23/2/11   blah blah blah 3.

wsInterface:

Cell A1: Mr A

ListBox (2 columns)
22/2/11   blah blah blah 1.
23/2/11   blah blah blah 3.

here are my codes in one of the macro:

Sub CustRemarkListBox_Change()
    Dim lbtarget As MSForms.ListBox
    Dim rngSource As Range
    Dim rw As Range
    Dim i As Long

    Set rngSource = Range("Remarks")

Set lbtarget = CustRemarkListBox

    With lbtarget
        .ColumnCount = 2
        .ColumnWidths = "50;200"
        For Each rw In rngSource.Rows
            If rw.Cells(1, 1) = Worksheets("ExistingCustomer").Range("C4") Then
                .AddItem ""
                For i = 1 To .ColumnCount
                    .List(.ListCount - 1, i - 1) = rw.Cells(1, i)
                Next
            End If
        Next
    End With
End Sub

The one in bold is my problem, it says "Object Required" meaning it did not detect my listbox in my Excel Worksheet that was named "CustRemarkListBox"

Are you using a Listbox from the Form Controls section? Those controls don't have a 'change event' or any events for that matter. Also, those objects aren't part of the MSForms library. Everything you seem to be doing is for ActiveX controls, so I want to be clear about what you're using. — Dick Kusleika

Martin S Martin S · Accepted Answer · 2011-03-01T14:16:14

So if I understand you correctly, the user has to enter a name into a cell, and you want the listbox to get populated based on the name entered?

You could use something like the Worksheet_Range event to run some code to populate the listbox when you enter a name it can match.

How familiar are you with VBA? You could use something like the following in the codpage for the relevant sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)  
If Target.Address = "A1" Then  
'put your code here  
End If  
End Sub

Excel VBA: Listbox

1 Answers