Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

populate separated range in listbox based on combobox

0

Hello

I  need to  show separated range  in listbox  for  each name when match name in combobox with name in column I 

I  got  code from the  intenet  and  try  modifying  but  gives mismatch error in this line 

 Lst = Application.Index(.Value2, Application.Transpose(Rws), Array(9, 10, 11, 12, 13))
Private Sub ComboBox1_Click()
   Dim Lst As Variant, Rws As Variant

   With Sheets("DAILY").Range("H1").CurrentRegion
      Rws = Filter(.Parent.Evaluate(Replace("transpose(if(@=" & Chr(34) & Me.ComboBox1 & Chr(34) & ",row(@),false))", "@", .Columns(10).Address)), False, False)
      Lst = Application.Index(.Value2, Application.Transpose(Rws), Array(9, 10, 11, 12, 13))
      If UBound(Rws) = 0 Then Lst = .Rows(Rws(0)).Value
   End With
   Me.ListBox1.List = Lst
End Sub

I put  picture how  could show on userfrom after select name from combobox and  match with  column I ,but  keep  in your  mind  in  the  picture  show TOTAL row  in first column in listbox   but  in range  in sheet  is  in column I , so  I  don't  mind  if  show  in second  or  firts column in listbox .the  most importand  should  also show  TOTAL row 

thanks in advance

excel
MS Office Version: 2019
posted by: Ali M (rep: 28) on Mon Jul 10, 2023 at 5:10 am
Answer
Discuss

Answers

0
Selected Answer

Ali

I can't work out what that code is attempting but suggest you instead try my revised file attached.

In that, I've commented out your code and added this (with explanatory comments):

Private Sub ComboBox1_Change()

    Dim Fnd As Range, Cl As Long, Rw As Long

    Me.ListBox1.Clear

    ' look for name
    With Sheets("DAILY").Range("I2:I1000")
        Set Fnd = .Find(Me.ComboBox1.Text, Lookat:=xlWhole)
    End With

    If Fnd Is Nothing Then Exit Sub

    ' if found, populate list
    ' get the block of data
    Set Fnd = Fnd.CurrentRegion
    ' loop from third row to end
    With Fnd
        For Rw = 3 To .Rows.Count
            ' add an item, formatting as date
            ListBox1.AddItem Format(.Cells(Rw, 1).Value, "dd/mm/yyyy")
            ' add other columns
            For Cl = 2 To 5
                ListBox1.List(ListBox1.ListCount - 1, Cl - 1) = .Cells(Rw, Cl).Value
            Next Cl
        Next Rw
    End With
End Sub

It's much longer but works well and now shows account details when you change the name.

Although it could have been shorter,  this gets the right date format for you (e.g. writing a range to the List avoids the loop but the date column is displayed as mm/dd/yyyy). 

I also initialised the UserForm with:

Private Sub UserForm_Initialize()
    ' set up columns
    With Me.ListBox1
        .ColumnCount = 5
        .ColumnWidths = "50;100;50;50;50"
        .TextAlign = fmTextAlignRight
    End With
End Sub

Hope this helps. If so, please remember to mark this Answer as Selected. 

posted by: John_Ru (rep: 6142) on Mon Jul 10, 2023 at 11:16 am
Discuss

Discussion

Hi John,
I can't work out what that code is attempting
this  is  not  problem . the  most  important  is  getting solution . at  least  your code is  easy  to  understand  than code above which  seem to  be  difficult  to  understanding. your  solution is  really great . thank you  so much
Ali M (rep: 28) Jul 10, '23 at 1:28 pm
Agreed and I'm glad that worked for you. Thanks for selecting my Answer, Ali. 
John_Ru (rep: 6142) Jul 10, '23 at 1:35 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login