Dynamic Range ComboBox show multiple columns

[platapuss82]

Background

I don’t 100% fully understand how VBA works but im normally able to get by copying code of the net and tweaking it for my needs. Currently im building a customer complaints system where the data entry is populated via a userform (as the users of the system aren’t that IT literate). A user will give each entry a status of “closed”, “escalated” and “in progress”.


Where an entry is “in progress” I need to be able to populate a combobox from a filtered list (show only entries that are classed as in progress). I also need it to display 3 columns within the combo box (ref, name, address) but im struggling. Ive found some code below that can be used however, its not dynamic

With Sheets("sheet1")

  Set rngSameAs = .Range("A2", .Range("A" & Rows.Count).End(xlUp).Offset(, 3))

  End With

 ComboBox1.List = rngSameAs.Value

So ive found a some code that uses a dynamic range however it only displays one column

Private Sub UserForm_Initialize()


  Dim myfilter As String

  Dim cell As Range, Thisrow As Long, Lastrow As Long

  Worksheets(1).AutoFilterMode = False 'turns off

  myfilter = "In progress"

  Range("TestMaterial").AutoFilter Field:=4, Criteria1:=myfilter

  ComboBox1.Clear

 

  'ComboBox1.RowSource = "TestMaterial"

  For Each cell In Range("TestMaterial")

  Thisrow = cell.Row

  If Not cell.Rows.Hidden And Thisrow <> Lastrow Then

  ComboBox1.AddItem cell.Value

  End If

  Lastrow = Thisrow

  Next cell

 End Sub

Is there a way I can use the second code but display the first 3 columns within the combobox?


Cheers


Jason

 

[prasaddn]

Hi Jason,

Try this:

ComboBox1.AddItem cell.Value & " " & cell.Offset(0, 1).Value & " " & cell.Offset(0, 2).Value

Regards,
PrasaD DN

 

[Hui]

Jason

Private Sub UserForm_Initialize()
  Dim myfilter As String
  Dim cell As Range
  Dim Thisrow As Long
  Dim Lastrow As Long
  Dim i As Integer
 
  Worksheets(1).AutoFilterMode = False 'turns off
  myfilter = "In progress"
  Range("TestMaterial").AutoFilter Field:=4, Criteria1:=myfilter
 
  Me.ComboBox1.Clear
  Me.ComboBox1.ColumnCount = 3
  i = 0
  For Each cell In Range("TestMaterial").Resize(, 1)
  Thisrow = cell.Row
  If Not cell.Rows.Hidden And Thisrow <> Lastrow Then
  ComboBox1.AddItem cell.Value
  ComboBox1.List(i, 1) = cell.Offset(0, 1).Value
  ComboBox1.List(i, 2) = cell.Offset(0, 2).Value
  i = i + 1
  End If
  Lastrow = Thisrow
  Next cell

 End Sub