Filling a ListBox Control (ActiveX)
In our forum, I answerd a question about ListBox control fo Excel.
http://puremis.net/excel/cgi-bin/yabb/YaBB.pl?num=1225116333
As additional tips, I'm writing this.
First of all, there are 2 kinds of controls for use in Excel worksheets.
The one is a contorl belongs to Forms, another is belongs ControlToolBox which is called ActiveX control. ActiveX control has been implemented since Excel 97, as the new control set which has more ritch functions.
This time, I'm talking about is ActiveX.
Since there are many ways to fill a ListBox control, it's depends that which way you should use for.
#1:ListFillRange
When the values which you'd like to fill a ListBox control have been placed in a worksheet, using the ListFillRange property would be the most easiest way.
ListBox1.ColumnCount = 2
ListBox1.ListFillRange = "Sheet1!A1:B10"
#2:AddItem
This is the most poplar way to add values one by one.
ListBox1.AddItem "item1"
ListBox1.AddItem "item2"
#3:List
If you have an array, you can simply set it as the List property.
The array shoud be a 2-d array. If already the ListFillRange has been set, you have to remove before execute this.
arr = Range("A1:B10").Value
ListBox1.Clear
ListBox1.ColumnCount = 2
ListBox1.List = arr
#4:Column
You can use the Column property as same as List property.
The difference is the Row and the Column are switched.
arr = Range("A1:B10").Value
ListBox1.Clear
ListBox1.ColumnCount = 10
ListBox1.Column = arr
http://puremis.net/excel/cgi-bin/yabb/YaBB.pl?num=1225116333
As additional tips, I'm writing this.
First of all, there are 2 kinds of controls for use in Excel worksheets.
The one is a contorl belongs to Forms, another is belongs ControlToolBox which is called ActiveX control. ActiveX control has been implemented since Excel 97, as the new control set which has more ritch functions.
This time, I'm talking about is ActiveX.
Since there are many ways to fill a ListBox control, it's depends that which way you should use for.
#1:ListFillRange
When the values which you'd like to fill a ListBox control have been placed in a worksheet, using the ListFillRange property would be the most easiest way.
ListBox1.ColumnCount = 2
ListBox1.ListFillRange = "Sheet1!A1:B10"
#2:AddItem
This is the most poplar way to add values one by one.
ListBox1.AddItem "item1"
ListBox1.AddItem "item2"
#3:List
If you have an array, you can simply set it as the List property.
The array shoud be a 2-d array. If already the ListFillRange has been set, you have to remove before execute this.
arr = Range("A1:B10").Value
ListBox1.Clear
ListBox1.ColumnCount = 2
ListBox1.List = arr
#4:Column
You can use the Column property as same as List property.
The difference is the Row and the Column are switched.
arr = Range("A1:B10").Value
ListBox1.Clear
ListBox1.ColumnCount = 10
ListBox1.Column = arr
