Add columns to multi-dimensional list box

Question

I have a two dimensional array (read in from a range in excel), then I would like to display columns 1, 3, 7, 8 in my Userform list box in one case and if a user clicks a button columns 2, 3, 6, 8, 9 should be shown.

So what is the best method to use column width to hide columns? How can I get the two-dimensional array to the list without looping?

Settings.excelArray = xlApp.Sheets("List").UsedRange.value
MOM.ListBox_Partslist.List = Settings.excelArray

Dim defaultSort() As Integer

defaultSort = Array(2, 3, 4, 5, 6)


'all columns width to 0, how to do?
For i = LBound(defaultSort) To UBound(defaultSort)
    With MOM.ListBox_Partslist
    '.ColumnCount = 54
    '.ColumnWidths = "0;60;0;0...."
...

DisplayName · Accepted Answer · 2018-04-20 13:20:16Z

2

I'd go this way:

fill your listbox with ALL columns
hide listbox unwanted columns

to do that, in your userfom code module:

at the very top place this:

Option Explicit

Dim ColumnWidths1 As String, ColumnWidths2 As String
Dim col As Long

as your UserForm_Initialize() event place this

Private Sub UserForm_Initialize()
    ColumnWidths1 = "50;0;50;0;0;0;50;50;0" ' adapt columns widths to suit your needs
    ColumnWidths2 = "0;50;50;0;0;50;0;50;50" ' adapt columns widths to suit your needs

    Settings.excelArray = xlApp.Sheets("List").UsedRange.value   
    With MOM.ListBox_Partslist
        .ColumnCount = UBound(Settings.excelArray, 2)
        .List = Settings.excelArray
        .ColumnWidths = ColumnWidths1
    End With
    col = 2
End Sub

as your button click event handler place this:

Private Sub CommandButton1_Click() 'change "CommandButton1" to your actual button name
    If col = 1 Then
        MOM.ListBox_Partslist.ColumnWidths = ColumnWidths1
        col = 2
    Else
        MOM.ListBox_Partslist.ColumnWidths = ColumnWidths2
        col = 1
    End If
End Sub

edited Apr 20, 2018 at 13:20

answered Apr 20, 2018 at 13:11

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

DisplayName Over a year ago

@skatun, any feedback?

skatun Over a year ago

I am still working on it... I figured out that I also needed to hide rows..

DisplayName Over a year ago

hiding rows is not part of your original question: please close this question by accepting this answer if it solves your original issue (hiding columns) and make a new post for any new issues. thank you

skatun Over a year ago

It turned out button click did not suit the end user and that I needed to swap over to combox, I keep you updated on the progress and post the working code once its done.

DisplayName Over a year ago

but there it remains this post with its requirement as it was: if it's been fulfilled you are asked to mark it as accepted. no relationship whatsoever with the changing of your end user request.

|

skatun · Accepted Answer · 2018-04-24 08:41:43Z

So I got to work with slightly modified version of DisplayName solution. Instead of using button I had to use a combox instead:

Public Sub initComboBox()

    Set Settings.filterArray = CreateObject("Scripting.Dictionary")

    Settings.filterArray.CompareMode = vbTextCompare

    Settings.filterArray.Add "MOM VIEW", Split("1,4,5,7,8", ",")
    Settings.filterArray.Add "COST VIEW", Split("1,4,5,7,12", ",")
    Settings.filterArray.Add "FILE VIEW", Split("1,4,5,7,18", ",")
    Settings.filterArray.Add "ALL", ""
    Dim cCont As control
    Dim cName As String


    Set cCont=MOM.ComboBox_FilterCol

    cName = replace(cCont.name, "ComboBox", "Array")

    cCont.List = Settings.comboBoxArray(cName)
    cCont.listIndex = UBound(cCont.List)



End Sub

Then I create my listbox:

MOM.ListBox_Partslist.Clear
Settings.excelArray = xlApp.Sheets("List").UsedRange.value
MOM.ListBox_Partslist.ColumnCount = UBound(Settings.excelArray, 2)
MOM.ListBox_Partslist.List = Settings.excelArray

And then I do the hiding of columns:

Public Sub changeFilter()
    Dim tempArray() As String
    Dim tempArray2() As Integer
    Dim width As Integer
    Dim columnWidths As String
    Dim i As Integer

    width = 100
    On Error Resume Next
    If MOM.ListBox_Partslist = Null Then Exit Sub

    ReDim tempArray2(UBound(MOM.ListBox_Partslist.List, 2)) As Integer

    columnWidths = ""
    With MOM.ComboBox_FilterCol
        If .value = "ALL" Then
             For i = LBound(tempArray2) To UBound(tempArray2)
                columnWidths = columnWidths & width & ","
            Next i
        Else

            tempArray = Settings.filterArray(.List(.listIndex))

            For i = LBound(tempArray) To UBound(tempArray)
                tempArray2(tempArray(i)) = width
            Next i

            For i = LBound(tempArray2) To UBound(tempArray2)
                columnWidths = columnWidths & tempArray2(i) & ","
            Next i
        End If
        MOM.ListBox_Partslist.columnWidths = Left(columnWidths, Len(columnWidths) - 1)
  End With
End Sub

Collectives™ on Stack Overflow

Add columns to multi-dimensional list box

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related