Populate ListBox with Column Headers

Nugget

New Member
Joined
Feb 2, 2009
Messages
25
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

bertie

Well-known Member
Joined
Jun 12, 2009
Messages
1,869
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.

Code:
[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

New Member
Joined
Feb 2, 2009
Messages
25
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

Well-known Member
Joined
Jun 12, 2009
Messages
1,869
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.

Code:
[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

New Member
Joined
Feb 2, 2009
Messages
25
Awesome, that's much nicer than the "break when changes" watch.
Thank you.
 

Forum statistics

Threads
1,136,775
Messages
5,677,659
Members
419,710
Latest member
Kvt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top