Excel VBA: Listbox

Question

Let's say I have a range of values as such:

Customer | Services | Cost  | Paid
Mel      | Abc      | $1.00 | TRUE
Mel      | Def      | $2.00 | FALSE
Xin      | Abc      | $3.00 | TRUE
Titus    | EEE      | $4.00 | TRUE

and I want these items to be inserted into a listbox. However I have a few criteria, which is to display the items only specific to the user (e.g. Mel or Xin or Titus), and display only when "False". How am I to do so, thanks in advance.

What I have now:

Dim lbtarget As MSForms.ListBox
Dim rngSource As Range
Set rngSource = Range("Table1")    
Set lbtarget = Me.ListBox1
With lbtarget
    'Determine number of columns
    .ColumnCount = 4
    'Set column widths
    .ColumnWidths = "50;80;100"
    'Insert the range of data supplied
    .List = rngSource.Cells.Value
End With

chris neilsen · Accepted Answer · 2011-02-23 13:05:58Z

2

Replace

.List = rngSource.Cells.Value

with

For Each rw In rngSource.Rows
    If rw.Cells(1,1) = <Specify User> And rw.Cells(1,4) = FALSE Then
        .AddItem ""
        For i = 1 To .ColumnCount
            .List(.ListCount - 1, i - 1) = rw.Value2(1, i)
        Next
    End If
Next

Whole Sub using Mel as the user

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

    Set rngSource = Range("Table1")
    Set lbtarget = Me.ListBox1
    With lbtarget
        .ColumnCount = 4
        .ColumnWidths = "50;80;100"
        For Each rw In rngSource.Rows
            If rw.Cells(1, 1) = "Mel" And rw.Cells(1, 4) = False Then
                .AddItem ""
                For i = 1 To .ColumnCount
                    .List(.ListCount - 1, i - 1) = rw.Cells(1, i)
                Next
            End If
        Next
    End With

End Sub

edited Feb 23, 2011 at 13:05

answered Feb 23, 2011 at 9:37

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Melvin Heng Over a year ago

hmm.. it says Run time error 451. Property let procedure not defined and property get procedure did not return an object.

chris neilsen Over a year ago

@Melvin Heng : on which line does it error? i've edited the answer to include a whole example just to be sure we're on the same wavelength

Fionnuala Over a year ago

AFAIK, you need to change rw.Value2(1, i) to rw.Cells(1, i)

chris neilsen Over a year ago

@Remou thanks, rw.Cells(1, i) works, but so does rw.Value2(1, i). FYI i tested in Excel 2007

Fionnuala Over a year ago

@chris is @Melvin using 2007? Because it does not work in 2000.

|

Collectives™ on Stack Overflow

Excel VBA: Listbox

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related