listbox population based on search value

[gkisacik]

Hi,

i have a database prepared where you can find added values from Column C onwards per Column A,

Column A = project numbers
Column B= project description

Column C = date
Column D = phase number
Column E = Member
Column F = Update

Column G = date
Column H= phase number
Column I =Member
Column J = Update
...
…
…

What I am trying to do is to populate listbox by searching project no listed on Column A. got basic code programme after searching on internet,

Can someone please help me with a right code?

Thanks,

Gokhan



Private Sub UserForm_Initialize()
Dim n As Integer
Dim ws As Worksheet
Set ws = Worksheets("Updates")
ws.Activate
Dim FindString As String
Dim Rng As Range
With ws.Range("A:A")
Dim LName As String
Dim LNamei As Long


For i = 3 To 100
LNamei = WorksheetFunction.VLookup(txtprojectno.Value, Worksheets("Updates"), i, False)
Next i
If LNamei = "" Or " " Then




With lbupdate


' .ColumnCount = ActiveSheet.Cells(1).CurrentRegion.Columns.Count
' .List = ActiveSheet.Cells(1).CurrentRegion.Value
.ColumnCount = 4
.AddItem
.Column(0, 0) = LNamei ' This is not working like this way unfortunately..
.Column(1, 0) = LNamei+1
.Column(2, 0) = LNamei+2
.Column(3, 0) = LNamei+3
.Column(1,0)....
End With




'With Application
'.WindowState = xlMaximized
'Zoom = Int(.Width / Me.Width * 100)
'Width = .Width
'Height = .Height
'End With
End If
End With
End Sub

[Kenneth Hobs]

Welcome to the forum!

While one can add each column/row to a listbox sort of like that, I like to speed things along using a filter scratch sheet method or an array method.

If you can attach a simple example XLSM file, it helps us help you more easily. Click the Go Advanced button lower right of a reply. Then click Manage Attachments link below reply box.

[gkisacik]

file is attached :-) thank you for the tip.

[Toubkal]

Hi all,

I wrote the code in the textbox1.keydown sub, try it and adapt it to your needs.
I manually set the property ColumnCount to 4 in the properties dialog.
Hope it helps.


Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim ws As Worksheet
Dim LastRow, itm
Dim I, ProjNo

If KeyCode = 13 Then
Set ws = Worksheets("Updates")

LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

ProjNo = UCase(Trim(frmupdate.TextBox1.Value)) 'upper case no spaces

itm = frmupdate.lbupdate.ListCount 'number of items already in the listbox

For I = 1 To LastRow
If UCase(Trim(ws.Cells(I, 1))) = ProjNo Then
If WorksheetFunction.CountA(ws.Range("C" & I & ":" & "F" & I)) > 3 Then 'the 4 cells from column C to F must contain data, that's why >3, you can change it.
frmupdate.lbupdate.AddItem
frmupdate.lbupdate.List(itm, 0) = ws.Cells(I, 3)
frmupdate.lbupdate.List(itm, 1) = ws.Cells(I, 4)
frmupdate.lbupdate.List(itm, 2) = ws.Cells(I, 5)
frmupdate.lbupdate.List(itm, 3) = ws.Cells(I, 6)
itm = itm + 1
Else
MsgBox "Empty data !"
End If
End If
Next I
End If
End Sub

[gkisacik]

Hi Toubkal,

Thank you for your help,
i have tested your code, it is a bit different than what i was looking for.
the userform should normally populate all the info per project no until the empty column. Instead it populates only the first 4 column info.

Any possibility to adap this??

Kind regards,

Gokhan

[Toubkal]

Hi,
Yes we can adapt it.

I see that you have a lot of columns in your sample file.
is the number of columns fixed or it can change?
And I speak french if it makes it easy for you.

[gkisacik]

hi,
yes there are columns to be filled in per project number, it is various number of columns, so it is not fixed unfortunately.
i would like to populate the listbox with previous (history) updates available in data.

In the meantime i am trying to change the format of data to see whether i could make it easier to make a code.

Unfortunately i am not able to speak french but i am learning every monday evening at a course :)

Cheers,

Gokhan

[Toubkal]

hi,
I made some minor changes to adapt it, I hope this is what you're looking for.



Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim ws As Worksheet
Dim LastRow, itm, LastCol
Dim I, ProjNo
Dim J, K
If KeyCode = 13 Then
Set ws = Worksheets("Updates")

LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
ProjNo = UCase(Trim(frmupdate.TextBox1.Value)) 'upper case no spaces

For I = 1 To LastRow
LastCol = ws.Cells(I, ws.Columns.Count).End(xlToLeft).Column 'Get the last non empty column in a each row so we loop through when adding data to the listbox

If UCase(Trim(ws.Cells(I, 1))) = ProjNo Then
frmupdate.lbupdate.Clear 'clear List for each request
itm = 0
J = 3 'first date in row
While J <= LastCol
frmupdate.lbupdate.AddItem
frmupdate.lbupdate.List(itm, 0) = ws.Cells(I, J) 'Date
frmupdate.lbupdate.List(itm, 1) = ws.Cells(I, J + 1) 'Project phase
frmupdate.lbupdate.List(itm, 2) = ws.Cells(I, J + 2) 'Member
frmupdate.lbupdate.List(itm, 3) = ws.Cells(I, J + 3) 'Update
itm = itm + 1
J = J + 4 'move to the next 4 data in the row.
Wend
End If
Next I
End If
End Sub


Good luck:)