listbox population based on search value

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

Code:

---

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

---

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.

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

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.

Code:

---

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

---

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

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.

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

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

Code:

---

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:)