Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Custom Fill in Listbox
I was watching a youtube video. ExcelVBAIsFun's video. http://www.youtube.com/watch?v=ulIraDmhF0o.
I followed his visual instruction (I am deaf so I cannot listen to him 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. It shows 7 columns. I want it to pull the information from first column in the database 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" When I hovered over that line, it says X = Empty. [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.ListCou nt - 1, 1) = Cells(x, 2) Me.lbxClientLookUp.List(Me.lbxClientLookUp.ListCou nt - 1, 2) = Cells(x, 3) Me.lbxClientLookUp.List(Me.lbxClientLookUp.ListCou nt - 1, 3) = Cells(x, 4) Me.lbxClientLookUp.List(Me.lbxClientLookUp.ListCou nt - 1, 4) = Cells(x, 5) Me.lbxClientLookUp.List(Me.lbxClientLookUp.ListCou nt - 1, 5) = Cells(x, 6) Me.lbxClientLookUp.List(Me.lbxClientLookUp.ListCou nt - 1, 6) = Cells(x, 7) End If Next x End Sub [code] Any assistant with this will be greatly appreciated. Amber Last edited by adketcham : August 18th 13 at 01:54 AM |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Fill in Listbox
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Fill in Listbox
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Listbox, fill by format | Excel Programming | |||
fill a listbox with data | Excel Programming | |||
Looping to fill ListBox | Excel Programming | |||
Unable to fill Listbox thru VBA | Excel Programming | |||
For..next.. help to fill listbox | Excel Programming |