Display listbox data based on Combobox value

vhdhfox

New Member
Joined
Aug 6, 2020
Messages
29
Office Version
  1. 2013
Platform
  1. Windows
Hi I'm trying to display data based on a combobox and data in Column D.
current code below

VBA Code:
Sub UserForm_Initialize()
   Dim lIndex As Long
   Dim rngMultiColumn As Range
   Set rngMultiColumn = ThisWorkbook.Worksheets("Sheet3").Range("A4:N100")
   With Me.AllocateBox1
      .ColumnCount = 14
      .ColumnWidths = "35;50;50;95;35;30;30;90;60;70;75;85;30"
      .List = rngMultiColumn.Cells.Value
       For lIndex = 0 To .ListCount - 1
          .List(lIndex, 0) = Format(.List(lIndex, 0), "dd-mmm")
       Next lIndex
   End With
End Sub
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
What type of data do you have in the combobox: text, number or date?
Do you want to load the data in the listbox where column D matches the combobox?
 

vhdhfox

New Member
Joined
Aug 6, 2020
Messages
29
Office Version
  1. 2013
Platform
  1. Windows
Its a combination or text and numbers (AA-1111-11)
I want it to display only the rows with matching value in Column D
so if (AA-1111-11) is chosen from the combobox then only the rows with that value in column D are to be displayed in the list box
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Try this. Change in the code ComboBox1 by the name of your combo.

VBA Code:
Private Sub ComboBox1_Change()
  Dim sh As Worksheet, cell As String
  Dim f As Range, r As Range
  Dim i As Long
  
  Set sh = Sheets("Sheet3")
  Set r = sh.Range("D4", Range("D" & Rows.Count).End(3))
  
  With AllocateBox1
    .Clear
    If ComboBox1.Value = "" Then
      .List = sh.Range("A4:N" & r.Rows.Count + 3).Value
      For i = 0 To .ListCount - 1
        .List(i, 0) = Format(.List(i, 0), "dd-mmm")
      Next i
      Exit Sub
    End If
    
    Set f = r.Find(ComboBox1.Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
      cell = f.Address
      Do
        .AddItem
        For i = 1 To 14
          .List(.ListCount - 1, i - 1) = sh.Cells(f.Row, i)
        Next i
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> cell
    End If
  End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,462
Messages
5,596,280
Members
414,051
Latest member
tabecker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top