Listobject .listcolumns in for loop

[Desantech]

Hello
Im am stucked, really tried hard to find out, since I am a newbee (not sooo new, but quite weak) I try to find a solution during learning by doing on my project
I know this is nasty and long, but the aim is simple:
Using Listobject on sheet I have:
1 userform
1 Listbox
11 checkboxes
1 textbox

I tried to "comment" in the VBA code what i tryy to do.

My aim is to get a listbox show the columns of a listobject according to the checkboxes. I am really new, no idea o programming, but did a lot today, I couldnt get it to work.
I know the code is nasty, I will simpify it, first need the tricks how to lol

Private Sub UserForm_Initialize()

Dim z As Integer
Dim alpha() As Variant
Dim x As Long
Dim i As Integer
Dim a As Variant


'_________________________________________________________________ SETUP CHECKBOXES (checkbox names are Checkbox1 to Checkbox11)


Set a = Sheets("Einstellungen_Asuche")

' On Userform "Artikelsuche2" I have 11 Checkboxes, and according to the values on sheet "a" Range M2:M12, I set the Checkboxes to true or false (there are the values stored)

With Artikelsuche2
    For i = 1 To 11
        .Controls("Checkbox" & i) = a.Range("M" & i + 1).Value
    Next i
End With
                        
' I set a counter "z" to see how many on the sheet "a" range "M+i" are true (I will get rid of this later on)

z = 1

For i = 1 To 11
    If a.Range("M" & i + 1).Value = True Then
        z = z + 1
    End If
Next i

' I correct the value because of i, z show me how many are truely TRUE

z = z - 1

'_________________________________________________________________ How many Columns to be shown in the listbox

' I set Listbox1 Columncount to number of "z" on Userform Artikelsuche2 (?)
    Artikelsuche2.ListBox1.ColumnCount = z
    
' I set a static pt width of each column (?)
    Artikelsuche2.ListBox1.ColumnWidths = "100"

' I give a name "myTable" to the Listobject found on sheet "Artikelsuche_Temp" called "myTable_Source")
    
    Set myTable = Worksheets("ArtikelSuche_Temp").ListObjects("myTable_Source")
    
' So, "myarray" will be the databodyrange of "myTable" that is the name variable of listobject "mytable_sourc"e what has a range of "A2:I234" in this case
    myarray = myTable.DataBodyRange

' here I am just testing and trying to figure out how to get the values done, like if Checbox 8 is marked, but nbothing else, it goes to Z = 8 and do something. I will get rid of this also
' for now, lets see if 4 checkboyes are checked TRUE, so lets jump to Z = 4:


'==============> SKIP

'If z = 1 Then
 '           For i = 1 To 11
  '          If a.Range("M" & i + 1).Value = True Then
   '
    '        myArray2 = myTable.ListColumns(i + 1).DataBodyRange
     '
      '      End If
       '     Next i
            
            
'ElseIf z = 2 Then
'
 '           myArray2 = Union(myTable.ListColumns(2).DataBodyRange, _
  '                      myTable.ListColumns(3).DataBodyRange)

'ElseIf z = 3 Then
 '
  '          ReDim alpha(0 To 11)
   '
    '        For i = 1 To 11
     '
      '          If a.Range("M" & i + 1).Value = True Then
       '
        '        alpha(x) = i + 1
'                x = x + 1
 '
  '          End If
   '         Next i
    '
     '       myArray2 = Union(myTable.ListColumns(alpha(0)).DataBodyRange, _
      '                  myTable.ListColumns(alpha(1)).DataBodyRange, _
       '                 myTable.ListColumns(alpha(2)).DataBodyRange)
                        
'<===============SKIP

'so if 4 checkboxes are Turned on, then we get here
'(this can be checkbox 1/4/7/9)

ElseIf z = 4 Then

            ReDim alpha(0 To 11)
            
' I start a counter from 1 to 11
            For i = 1 To 11
            
' If the "stored range" on sheet "a" are true, then I put into alpha array the value it found the true value on, and increase to next array (x+1) before moving on to next i
                If a.Range("M" & i + 1).Value = True Then
                    alpha(x) = i + 1
                x = x + 1
            End If
            Next i
        
' Here the problem occurs:
' As I have the listcolumns (alpha numbers) okay, regarding to what I want, this UNION doesnt put the columns into it, only then, if the columns are next to eachother, exampl: col 1-2-3-4, but not 1-4-6-9

            myArray2 = Union(myTable.ListColumns(alpha(0)).DataBodyRange, _
            myTable.ListColumns(alpha(1)).DataBodyRange, _
            myTable.ListColumns(alpha(2)).DataBodyRange, _
            myTable.ListColumns(alpha(3)).DataBodyRange)
                                                       
End If


    Artikelsuche2.ListBox1.List = myArray2
    Artikelsuche2.ListBox1.ColumnHeads = True
    Artikelsuche2.Show
    Artikelsuche2.TextBox1.Text = Trim(ArtikelSuche.TextBox3.Text)
    

    Artikelsuche2.TextBox1.SetFocus
.
.
.
.

Well I thank you for your help

 

[John_w]

UserForm1.ListBox1.ColumnHeads = True
UserForm1.ListBox1.List = myarray2

Do you get column headers with that method?

 

[Desantech]

Hello not yet

That's another thread, but I post the solution if I get there... Quite busy now with graphic jobs, I be back in approx 3 days

 

[John_w]

I think you only get column headers if ColumnHeads = True AND the RowSource property is set, not with the List property.