Consulting

Results 1 to 8 of 8

Thread: listbox population based on search value

  1. #1
    VBAX Newbie
    Joined
    Oct 2018
    Posts
    4
    Location

    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

    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

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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.

  3. #3
    VBAX Newbie
    Joined
    Oct 2018
    Posts
    4
    Location
    file is attached :-) thank you for the tip.
    Attached Files Attached Files

  4. #4
    VBAX Regular
    Joined
    Oct 2018
    Posts
    18
    Location
    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

  5. #5
    VBAX Newbie
    Joined
    Oct 2018
    Posts
    4
    Location
    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

  6. #6
    VBAX Regular
    Joined
    Oct 2018
    Posts
    18
    Location
    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.

  7. #7
    VBAX Newbie
    Joined
    Oct 2018
    Posts
    4
    Location
    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

  8. #8
    VBAX Regular
    Joined
    Oct 2018
    Posts
    18
    Location
    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
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •