Assign values to list box array

duncanmcl

New Member
Joined
Aug 7, 2012
Messages
19
Trying to create a list box with values in a column = value . Using VBA I was able to cycle true the records = value...in trying to populate a list box with those field values...error...it seems an array was needed to be defined....found code and adapted to my logic and it populated list box with values row1, col2, col3, etc row2, col2 etc. Can't see logic to assign my values to each for my match process mentioned above. Let me qualify my lack of good VBA code.....my experience is from Access VBA and I find Excel VBA very hard

Private Sub LoadBCDetail()
Me.BCDetail.ColumnCount = 5
Me.BCDetail.ColumnWidths = ".5in;.5in;.5in;.5in;.5in"
' populating variable
Const rows = 30
Const cols = 5
Dim listarray(1 To rows, 1 To cols) As Variant
For Row = 1 To rows
For col = 1 To cols
If col = 1 Then
listarray(Row, col) = "row" & Row 'ActiveCell(I, 0).Value '"row" & row
Else
listarray(Row, col) = "col" & col 'ActiveCell(I, 1).Value '"col" & col
End If
Next col
Next Row
'Transferring values from array to Prjlist.
Me.BCDetail.List = listarray
Me.BCDetail.ListIndex = -1 'No item selected
End Sub

Private Sub UserForm_Initialize()
Dim cLoc2 As Range
Dim zZ As Long
Dim PayeeN As String
PayeeN = "003943"
FindLastRow2
Set Ws2 = Worksheets("q_report_detail")
Dim I As Long
I = 2
'With Me.BCDetail
Zzz = 2
With Ws2
'MsgBox ActiveCell(I, 1).Value & "-" & PayeeN
Do While I < 100 'ActiveCell(I, 1).Value = PayeeN 'And Not .EOF 'And I < 10 '= PayeeN 'Ws2.Range("payeebc").Value = PayeeN
rownumber = Zzz
LoadBCDetail
With listarray
If ActiveCell(I, 1) = PayeeN Then
End If
I = I + 1
End With
Loop
End With
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,059
Office Version
  1. 365
Platform
  1. Windows
What match?

Are you trying to only populate the listbox with data from rows where a particular column is a particular value?

eg all the rows with 'Frdge' in the first column
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,059
Office Version
  1. 365
Platform
  1. Windows
Then you should just need an If and a little work with the array.

Something like this perhaps.
Code:
Dim NoRows As long

For Row = 1 To rows
For col = 1 To cols
If Cells(Row, col).Value = "Fridge" Then
     RowNo = RowNo+1
     listarray(RowNo, col) = Cells(Row, col).Value
     
End If


Next col
Next Row
 

duncanmcl

New Member
Joined
Aug 7, 2012
Messages
19

ADVERTISEMENT

thanks....still no values in list box....using your code....if fridge is in col2....should I specify in your if cell stmt....i named col2 payeen?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,059
Office Version
  1. 365
Platform
  1. Windows
Sorry I don't quite understand.

Perhaps you could post some sample data, indicating which rows should go in the listbox.
 

duncanmcl

New Member
Joined
Aug 7, 2012
Messages
19

ADVERTISEMENT

below is current code...the ist bos is named bcdetail, the matching value is in col2....it runs w/no data displayed when the me.bcdetail.list line is commented out...otherwise an error=could not set the list property...appreciate your help!!
Private Sub UserForm_Initialize()
Dim cLoc2 As Range
Dim zZ As Long
Dim PayeeN As String
PayeeN = "000000001039"

FindLastRow2
Set Ws2 = Worksheets("q_report_detail")
Dim I As Long
I = 2
'With Me.BCDetail
Zzz = 2
With Ws2
With Me.BCDetail
Dim NoRows As Long
Const cols = 5
Const rows = 10
Dim listarray() As Variant
For Row = 1 To rows
For col = 1 To cols
If Cells(Row, 2).Value = "000000001039" Then
RowNo = RowNo + 1
listarray(RowNo, col) = Cells(Row, col).Value
MsgBox "matched" & Cells(Row, col)
End If
Next col
Next Row
End With
End With
Me.BCDetail.List = listarray
End Sub
 

duncanmcl

New Member
Joined
Aug 7, 2012
Messages
19
34000000001039505005361001GLENCOE MANAGEMENT8/1/2001AE77
34000000001039505005361002GLENCOE MGMT/HIGHLAND ENTERPRI8/1/2001AE1717

<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 48pt" span=6 width=64><TBODY>
</TBODY>
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,569
Office Version
  1. 2010
Platform
  1. Windows
below is current code...the ist bos is named bcdetail, the matching value is in col2....it runs w/no data displayed when the me.bcdetail.list line is commented out...otherwise an error=could not set the list property...appreciate your help!!
I am also having trouble figuring out what you want. From your code, it looks like the value you want to find is always in Column B. Assuming that is correct, and assuming the word you want to find is "Fridge", show us what the ListBox contents should look like if this is your data (I highlighted the word Fridge in red in Column B so it would be easier for you to spot)...

ABCDE
1DataA1DateB1DataC1DataD1DataE1
2DataA2FridgeDataC2DataD2DataE2
3DataA3DateB3DataC3DataD3DataE3
4DataA4DateB4DataC4DataD4DataE4
5DataA5FridgeDataC5DataD5DataE5
6DataA6FridgeDataC6DataD6DataE6
7DataA7DateB7DataC7DataD7DataE7
8DataA8FridgeDataC8DataD8DataE8
9DataA9DateB9DataC9DataD9DataE9

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
</TBODY>
 

Watch MrExcel Video

Forum statistics

Threads
1,118,507
Messages
5,572,561
Members
412,470
Latest member
nirortal
Top