Populate ListBox with Column Headers

[Nugget]

Hi,
I have a row of data that I want to use in a ListBox.
Lets say Row1 Column 1 to Column14 - "A1:N1"
I have the Range three ways:
A horizontal named range "Choices"
A horizontal array of the Values of the range.
A vertical array of the Values of the range.

I am trying to populate a listbox on a form so that I can select a value which will then select the cell of that value so that I can run a macro on the column.

I am trying to avoid the "clutter" of having an extra column on the worksheet with the transposed values.


Dim These_Cols as Long
These_Cols = Cells(1, Columns.Count).End(xlToLeft).Column

Range("A1").Select
Set Choices = Range(Selection, Selection.End(xlToRight))
ThisWorkbook.Names.Add Name:="Choices", RefersTo:=Choices

Dim Choices1() As Variant
ReDim Choices1(These_Cols) As Variant
Dim j As Long
For j = 2 To These_Cols
Choices1(j - 2) = Cells(2, j).Value
Next j
ThisWorkbook.Names.Add Name:="Choices1", RefersTo:=Choices1

Dim Choices2() As Variant
ReDim Choices2(These_Cols, 0) As Variant
Dim j As Long
For j = 2 To These_Cols
Choices2(j - 2, 0) = Cells(2, j).Value
Next j
ThisWorkbook.Names.Add Name:="Choices2", RefersTo:=Choices2

For the user form:

Private Sub UserForm_Initialize()

Set rnData = ActiveSheet.Range("Choices2")
vaData = rnData.Value

With Me.ListBox1
.Clear
.List = vaData
.ListIndex = -1
End With

End Sub

 

[bertie]

Here is a way to populate the listbox. I have put the code where you detrmine the value selected by the user behind a command button.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()
   [COLOR=darkblue]Dim[/COLOR] vaData [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
 
   vaData = Range("Choices").Value
   [COLOR=darkblue]For[/COLOR] i = [COLOR=darkblue]LBound[/COLOR](vaData) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](vaData, 2)
      Me.ListBox1.AddItem vaData(1, i)
   [COLOR=darkblue]Next[/COLOR] i
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click()
   [COLOR=darkblue]Dim[/COLOR] val [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
 
   [COLOR=darkblue]For[/COLOR] i = 0 [COLOR=darkblue]To[/COLOR] ListBox1.ListCount
      [COLOR=darkblue]If[/COLOR] ListBox1.Selected(i) = [COLOR=darkblue]True[/COLOR] [COLOR=darkblue]Then[/COLOR] val = ListBox1.List(i)
   [COLOR=darkblue]Next[/COLOR] i
 
   [COLOR=green]'unload the form after obtaining the selected listbox value[/COLOR]
   Unload Me
   MsgBox val
 
[COLOR=#008000] '================[/COLOR]
[COLOR=#008000] 'rest of code goes here[/COLOR]
[COLOR=#008000] '================[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[Nugget]

Thank you that got it.

Am I reading it right that between a macro and a userform the rows and columns switch?

ie, ReDim Choices(These_Cols, 0) As Variant
and,
Me.ListBox1.AddItem vaData(1, i)

 

[bertie]

To get a visualization of how the array looks insert a stop command.

When the code stops, if it is not visible open the Locals Window, View => Locals Window

Expand the array variable to see how it stores the values.

[COLOR=#00008b]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()
   [COLOR=darkblue]Dim[/COLOR] vaData [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
 
   vaData = Range("Choices").Value
[COLOR=red]Stop[/COLOR]
   [COLOR=darkblue]For[/COLOR] i = [COLOR=darkblue]LBound[/COLOR](vaData) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](vaData, 2)
      Me.ListBox1.AddItem vaData(1, i)
   [COLOR=darkblue]Next[/COLOR] i
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[Nugget]

Awesome, that's much nicer than the "break when changes" watch.
Thank you.