VBA - Filtered list to ComboBox with multiple columns

0
votes

I've got an Excel sheet that takes a range filtered on multiple columns dependant on buttons and populates a combo-box.

The idea is I'm building a product picker with multiple categories (can be part of multiple categories).

VBA is not my area and the solution that I've got, after a couple of days of searching, is very inelegant.

Problems I'm facing:

  • General lack of knowledge handling types in VBA e.g range, arrays, etc.
  • SpecialCells(xlCellTypeVisible) -> not behaving as I would have thought -- Consequently having to have multiple sorts to get data into combo box

PseudoCode:

  1. Activate Worksheet and copy named range to a oldRange
  2. Select -> This way I can see what is happening.
  3. Disable autofilter if already set 'to clear
  4. Sort and filter with autofilter
  5. select -> So I can see what's happening
  6. set to newRange using oldRange.offset(1,0).SpecialCells(xlCellTypeVisible) -> get rid of headers and filtered rows
  7. newArray = newRange
  8. combobox.list = newArray

Spreadsheet: The data is comming from multiple tabs (selected by RadioButtons) that are formatted differently Require 11 columns to be displayed in the comboBox

Question: Can anyone answer why the newRange=oldRange.SpecialCells(xlCellTypeVisible) doesn't transfer to the combobox.list properly? I can see in the excel window behind the form that the autofilter is working and then selecting the correct data. The problem I face is that it populates the ComboBox list until the first hidden/filtered value and then stops. This means I can get it to work by sorting first but I'm not confident that I'll be able to scale this up with multiple sorts & multiple filters that avoid any hidden values between good values (causing the comboBox list to be incomplete)...

Though this might work, I feel like I'm making a meal of it, is there a simpler way to get these values in to the combo Box.

Thanks In Advance

1
vbaexcelcombobox

1 Answers

0
votes

Make sure that the ColumnCount property is set properly. If it is set to 1, it will show only one column.

Better you set the ColumnCount property of the combobox during the UserForm Initialize event like this...

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Dim oldRange As Range

    Set ws = Sheets("Sheet1")
    Set oldRange = ws.Range("A1").CurrentRegion

    Me.ComboBox1.ColumnCount = oldRange.Columns.Count
End Sub