Custom Fill in Listbox

[adketcham]

I was watching a youtube video. ExcelVBAIsFun's video.

.

I followed his visual instruction (I am deaf so I cannot listen to hi
talk). I have this error message when I run the button.

I have this client search page on one sheet and the database on other.
I saved the database as namebox and used the listbox to link to it. I
shows 7 columns.

I want it to pull the information from first column in the databas
sheet to the listbox (in client search page).

Error message is this, "Run-time error'-2147467259 (80004005)'
Unspecified error.

When I clicked debug, it yellow highlighted "Me.lbxClientLookUp.Clear"
When I deleted it. It went to the next line. "For x = 2 To lr"


Code
-------------------

Private Sub cmbClientSearch_Click()
crit = Range("C4")

If Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
lr = 2
Else
lr = Cells(Rows.Count, 1).End(xlUp).Row
End If

Me.lbxClientLookUp.Clear

For x = 2 To lr
If Cells(x, 2) = crit Then
'write it to lbx
Me.lbxClientLookUp.AddItem Cells(x, 1)
Me.lbxClientLookUp.List(Me.lbxClientLookUp.ListCount - 1, 1) = Cells(x, 2)
Me.lbxClientLookUp.List(Me.lbxClientLookUp.ListCount - 1, 2) = Cells(x, 3)
Me.lbxClientLookUp.List(Me.lbxClientLookUp.ListCount - 1, 3) = Cells(x, 4)
Me.lbxClientLookUp.List(Me.lbxClientLookUp.ListCount - 1, 4) = Cells(x, 5)
Me.lbxClientLookUp.List(Me.lbxClientLookUp.ListCount - 1, 5) = Cells(x, 6)
Me.lbxClientLookUp.List(Me.lbxClientLookUp.ListCount - 1, 6) = Cells(x, 7)

End If

Next x


End Sub

Code
-------------------


Any assistant with this will be greatly appreciated.

Ambe

 

[GS]

I recommend a different approach where the search criteria is entered
via an InputBox rather than stored on the sheet so programtic control
is easier to manage/maintain. You could use a combobox on a userform if
you want to control user selection, though!

That said, I made a data table with 10 rows and 7 cols, then ran the
following code in the button Click event.

Private Sub btnClientSearch_Click()
Dim vDataIn, n&, k&, Ndx&, vAns
vAns = InputBox("Enter the client ID to search for")
If vAns = Empty Then Exit Sub
vDataIn = Me.UsedRange
With Me.lstClientSearch
.Clear: .ColumnCount = UBound(vDataIn, 2)
For n = LBound(vDataIn) To UBound(vDataIn)
If vDataIn(n, 2) = vAns Then
.AddItem vDataIn(n, 1)
For k = 0 To UBound(vDataIn, 2) - 1
.List(Ndx, k) = vDataIn(n, k + 1)
Next 'k
Ndx = Ndx + 1
End If
Next 'n
End With
End Sub

Note that I used my own naming convention for the control type prefix.
You can change this to suit your preference OR rename your controls
accordingly.

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

If your sheet layout requires other cell content outside the data table
the you could give the table a name (sheet level) and use that to load
the array...

Private Sub btnClientSearch_Click()
Dim vDataIn, n&, k&, Ndx&, vAns
vAns = Me.Range("C4").Value
If vAns = Empty Then Exit Sub
vDataIn = Me.Range("DataSrc")
With Me.lstClientSearch
.Clear: .ColumnCount = UBound(vDataIn, 2)
For n = LBound(vDataIn) To UBound(vDataIn)
If vDataIn(n, 2) = vAns Then
.AddItem vDataIn(n, 1)
For k = 0 To UBound(vDataIn, 2) - 1
.List(Ndx, k) = vDataIn(n, k + 1)
Next 'k
Ndx = Ndx + 1
End If
Next 'n
End With
End Sub

...where the range name "DataSrc" was defined (in the NameBox left of
the FormulaBar) as...

'sheet1'!DataSrc

...and assumes the data table is on Sheet1. Note that the sheetname is
wrapped in apostrophes, and the defined name is preceeded by the
exclamation character.

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion