listbox items = column headers macro

[tuspilica]

Hello friends,

I have a code that helps me to open a list box from where i can choose items arranged in column A, sheet2.
In column A, sheet2 i have: countries, cities, cars, fruits.
In Sheet1 i have 2 buttons, when clicking <Let's go button>, a list box is open and i'm able to choose items that are inserted in column H, sheet1.

Let's consider this items now are header columns, under Countries i have: UK, Albania, Egypt, Argentina, Canada, Mongolia, Tanzania and so on.
The challange is how can i add in column H, sheet1, one after another, if i want to select in listbox Countries and Cars columns, sheet2?

Keeping in listbox just the header columns, when i choose one or more of them, after clicking OK button, on column H to get all items located under the selected header columns.

Attached is an example, but it works only for Header columns, not for what is under each header column.

Thank you

[SamT]

There are at least 42 ways to accomplish what you want. Which method is most appropriate depends on many factors including how you want to display the selected lists and how many lists you have.

In your situation, displaying only 5 fixed length lists on a new UserForm, I would Hard Code 5 Empty Listboxes on the new UserForm. Then I would assign their RowSources and un-hide them during Run Time.

[tuspilica]

Hello SamT,

There are 22 columns with different number of rows each. Some columns have 1 row other 10 rows.

I make a list with all these column headers into one single column. This column i would like to be displayed in listbox.
But, if i select 7 items, after clicking Ok button, i would like to get all rows under the associated column header, all arranged into one new column.

Any other suggestions to get selected columns into one final column are much welcomed.

Thanks

[tuspilica]

It's more easy to populate the listbox will all the rows added in one single column and select from there, but beeing so many rows, and without any possibility to search inside the listbox, it will be hard to navigate and find relavant data to be selected.
So, i thought it's more simple to put inside the listbox only the head columns.

[SamT]

In Sub UserForm_Initialize()

ListBoxMain.List = Transpose(Range(Range("A1"), Cells(1, Columns.Count).End(xlToRight))
CreateNewNamesFromColumnHeaders SheetName:="Sheet2", StartColumn:=3, UseHeaders:=True

Put this in a Standard Module

Sub CreateNewNamesFromColumnHeaders(SheetName As String, _
                                    Optional StartColumn As Long = 1, _
                                    Optional IncludeHeader As Boolean = False)
'Creates Sheet Specific Named Ranges.
'Deletes spaces from Headers to create Names
'Allows empty columns or multiple Tables.
'Call From Sheet Change Event to handle Dynamic Lists

Dim Headers As Range
Dim Cel As Range
Dim Nme As Name
Dim strRefersTo As String

With Sheets(SheetName)
  'Clear all Sheet Specific Names
  For Each Nme In .Names
    Nme.Delete
  Next
  
  Set Headers = Range(Cells(1, StartColumn), Cells(1, Columns.Count).End(xlToRight))
  
  For Each Cel In Headers
    If Cel <> "" Then
      strRefersTo = "=" & SheetName & "!" & Range(Cel.Offset(Abs(IncludeHeader)), Cel.End(xlDown)).Address
      .Names.Add Name:=SheetName & "!" & Replace(Cel, " ", ""), RefersTo:=strRefersTo
    End If
  Next Cel
End With
End Sub

Now when you create a new Listbox from a selection you can

'Not Code, just a Crude example
NewRowSource = "Sheet2!" & Replace(Selection, " ", "")

[tuspilica]

it's working.
thank you so much SamT.