Combox box question
it's clumsy but your add item loop could include all your
combos
for each item in mylist
.additem item.value
sheet1.combo1.additem item.value
userform2.combo3.additem item.value
next
another way would be to set the combo's source from a
named range
In the following demo I have an ActiveX Combo on one
sheet, and ActiveX Listbox on another sheet and a
combobox on a userfom
The code below is on a standard module...the procedure
Initialise fetches data from a "database", pushes it into
a column that gets range named, then assigns the name to
the source of the three objects. on your active sheet,
range name one cell as "MyList" to begin with.
Option Explicit
Public Sub inititialise()
Dim col As New Collection
Dim item
Dim index As Long
Dim rTarget As Range
Set rTarget = Sheet2.Range("MyList")
With rTarget
.Clear
.Resize(1, 1).Name = "MyList"
End With
Set rTarget = Sheet2.Range("MyList")
Set col = GetFromDatabase
For Each item In col
rTarget.Offset(index, 0).Value = item
index = index + 1
Next
rTarget.Resize(index, 1).Name = "MyList"
Sheet1.ComboBox1.ListFillRange = "MyList"
Sheet2.ListBox1.ListFillRange = "MyList"
UserForm1.ComboBox1.RowSource = "MyList"
UserForm1.Show vbModeless
End Sub
Private Function GetFromDatabase() As Collection
Dim c As New Collection
With c
.Add "Mike"
.Add "Patrick"
.Add "Ivan"
.Add "Fola"
.Add "Nit"
End With
Set GetFromDatabase = c
Set c = Nothing
End Function
Hpe this demo gives you some ideas
Patrick Molloy
Microsoft Excel MVP
-----Original Message-----
Hi,
Is there a way to populate more than one combobox with
the same information
apart from just repeating the code?
At the moment I'm using the additem function.
Many thanks
David
.
|