Hello.

I am trying to add a list to an ActiveX ComboBox on the sheet itself. I am
able to do this using a Userform:

For Each Item In MyList
MyForm.MyComboBox.AddItem Item
Next Item

This works great... for Userforms. I'm having trouble with the correct
syntax for a ComboBox residing on the sheet, and not in a Userform. Can
anyone out there help?

Thanks.

Dan

for each item in Mylist
Activesheet.Combobox1.AddItem item
Next

or

assume the OleObject name of the combobox is MyCombobox (in xl2000 and
later, the name of the combobox and the oleobject name should be the same.

for each item in List
Activesheet.OleObjects("MyComboBox").Object.AddIte m Item
Next


Here is a tested Example:

Sub AddStuff()
Dim rng as Range
Dim cell as Range
With Worksheets("Sheet8")

Set rng = .Range(.Cells(1, 1), _
.Cells(1, 1).End(xlDown))
For Each cell In rng
.OLEObjects("MyComboBox").Object _
.AddItem cell.Value
Next
End With
End Sub

--
Regards,
Tom Ogilvy


Dan Gesshel wrote in message
...
Hello.

I am trying to add a list to an ActiveX ComboBox on the sheet itself. I am
able to do this using a Userform:

For Each Item In MyList
MyForm.MyComboBox.AddItem Item
Next Item

This works great... for Userforms. I'm having trouble with the correct
syntax for a ComboBox residing on the sheet, and not in a Userform. Can
anyone out there help?

Thanks.

Dan

Hmm... well, I'm trying different variations of this and I keep coming up
with a Permission Denied error (which I don't recall seeing before.) Here's
what I have:

Dim MyCollection As New Collection

For Each Item In MyCollection
ActiveSheet.OLEObjects("MyComboBox").Object.AddIte m Item
Next Item

If I change this and add:

ActiveSheet.OLEObjects("MyComboBox").Object.AddIte m Item.Value
(adding .Value)

I receive an Object Required error. Normally I can muck my way through
these, but this time I'm having some problems getting it.

Any additional help Tom would be great.

Thanks

Dan


"Tom Ogilvy" wrote in message
...
for each item in Mylist
Activesheet.Combobox1.AddItem item
Next

or

assume the OleObject name of the combobox is MyCombobox (in xl2000 and
later, the name of the combobox and the oleobject name should be the same.

for each item in List
Activesheet.OleObjects("MyComboBox").Object.AddIte m Item
Next


Here is a tested Example:

Sub AddStuff()
Dim rng as Range
Dim cell as Range
With Worksheets("Sheet8")

Set rng = .Range(.Cells(1, 1), _
.Cells(1, 1).End(xlDown))
For Each cell In rng
.OLEObjects("MyComboBox").Object _
.AddItem cell.Value
Next
End With
End Sub

--
Regards,
Tom Ogilvy


Dan Gesshel wrote in message
...
Hello.

I am trying to add a list to an ActiveX ComboBox on the sheet itself. I
am
able to do this using a Userform:

For Each Item In MyList
MyForm.MyComboBox.AddItem Item
Next Item

This works great... for Userforms. I'm having trouble with the correct
syntax for a ComboBox residing on the sheet, and not in a Userform. Can
anyone out there help?

Thanks.

Dan

Here is an adaptation of some John Walkenbach code. In A1:A105 I have a
random list of the uppercase letters of the alphabet. This puts A - Z in
order into the combobox. The combobox name is Combobox1

Option Explicit
' This example is based on a tip by J.G. Hussey,
' published in "Visual Basic Programmer's Journal"

Sub RemoveDuplicates()
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item

' The items are in A1:A105
Set AllCells = Range("A1:A105")

' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Note: the 2nd argument (key) for the Add method must be a string
Next Cell

' Resume normal error handling
On Error GoTo 0

' Sort the collection (optional)
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, befo=j
NoDupes.Add Swap2, befo=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

' Add the sorted, non-duplicated items to a ListBox
For Each Item In NoDupes
ActiveSheet.OLEObjects("Combobox1").Object.AddItem Item
Next Item

End Sub

--
Regards,
Tom Ogilvy

"Dan Gesshel" wrote in message
...
Hmm... well, I'm trying different variations of this and I keep coming up
with a Permission Denied error (which I don't recall seeing before.)
Here's
what I have:

Dim MyCollection As New Collection

For Each Item In MyCollection
ActiveSheet.OLEObjects("MyComboBox").Object.AddIte m Item
Next Item

If I change this and add:

ActiveSheet.OLEObjects("MyComboBox").Object.AddIte m Item.Value
(adding .Value)

I receive an Object Required error. Normally I can muck my way through
these, but this time I'm having some problems getting it.

Any additional help Tom would be great.

Thanks

Dan


"Tom Ogilvy" wrote in message
...
for each item in Mylist
Activesheet.Combobox1.AddItem item
Next

or

assume the OleObject name of the combobox is MyCombobox (in xl2000 and
later, the name of the combobox and the oleobject name should be the
same.

for each item in List
Activesheet.OleObjects("MyComboBox").Object.AddIte m Item
Next


Here is a tested Example:

Sub AddStuff()
Dim rng as Range
Dim cell as Range
With Worksheets("Sheet8")

Set rng = .Range(.Cells(1, 1), _
.Cells(1, 1).End(xlDown))
For Each cell In rng
.OLEObjects("MyComboBox").Object _
.AddItem cell.Value
Next
End With
End Sub

--
Regards,
Tom Ogilvy


Dan Gesshel wrote in message
...
Hello.

I am trying to add a list to an ActiveX ComboBox on the sheet itself.
I
am
able to do this using a Userform:

For Each Item In MyList
MyForm.MyComboBox.AddItem Item
Next Item

This works great... for Userforms. I'm having trouble with the correct
syntax for a ComboBox residing on the sheet, and not in a Userform.
Can
anyone out there help?

Thanks.

Dan