I'd like to place a combo box on my worksheet, but I don't want to us
the rowsource property to populate the static dropdown list I need.
How can I set up the static values for the drop down list.

I am currently just using:

with combobox1
.additem " "
.additem "FULL"
.additem "NET"
.additme "MOD"
end with

Unfortunately whenever I use the ComboBox_Change() sub it keeps addin
the same items to the list and I go from 4 items to 8, then to 12
etc.... Any ideas on how I can just set the initial values with VB
without utilizing row source for the static list?

Thanks....A

--
Message posted from http://www.ExcelForum.com

Don't add the items in the Change event, do it from another separate macro.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"AJ Master " wrote in message
...
I'd like to place a combo box on my worksheet, but I don't want to use
the rowsource property to populate the static dropdown list I need.
How can I set up the static values for the drop down list.

I am currently just using:

with combobox1
additem " "
additem "FULL"
additem "NET"
additme "MOD"
end with

Unfortunately whenever I use the ComboBox_Change() sub it keeps adding
the same items to the list and I go from 4 items to 8, then to 12,
etc.... Any ideas on how I can just set the initial values with VBA
without utilizing row source for the static list?

Thanks....AJ


---
Message posted from http://www.ExcelForum.com/

Bob,

I'm not adding the items in the change event. I have a sub calle
"ComboBox_Initialize" that contains the additems method.

Any other thoughts why this is happening?

A

--
Message posted from http://www.ExcelForum.com

Sub ComboBox_Initialize()
combobox1.clear
' now add the items

End Sub

--
Regards,
Tom Ogilvy


"AJ Master " wrote in message
...
Bob,

I'm not adding the items in the change event. I have a sub called
"ComboBox_Initialize" that contains the additems method.

Any other thoughts why this is happening?

AJ


---
Message posted from http://www.ExcelForum.com/

Others have suggested Clear, but surely you are not repeatedly calling this
sub are you?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"AJ Master " wrote in message
...
Bob,

I'm not adding the items in the change event. I have a sub called
"ComboBox_Initialize" that contains the additems method.

Any other thoughts why this is happening?

AJ


---
Message posted from http://www.ExcelForum.com/

Hi,

Sub TEST() 'ComboBox_Initialize()

With ComboBox1
.Clear
.List = Array(" ", "FULL", "NET", "MOD")
End With

End Sub


--
Regards,
Soo Cheon Jheong
_ _
^ ^
~

Thanks to all for your help. One last question.

When I open the workbook, the combobox is not populated, even though
the code is there. I would prefer not to run some macro to run the
"ComboBox_initialize" sub.

How can I make sure this combobox list is populated when I open the
workbook/template?

Thanks...again.

AJ


---
Message posted from http://www.ExcelForum.com/

Add it to the Workbook_Open event in the Thisworkbook code module.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"AJ Master " wrote in message
...
Thanks to all for your help. One last question.

When I open the workbook, the combobox is not populated, even though
the code is there. I would prefer not to run some macro to run the
"ComboBox_initialize" sub.

How can I make sure this combobox list is populated when I open the
workbook/template?

Thanks...again.

AJ


---
Message posted from http://www.ExcelForum.com/

Bob,

I added the code to the workbook open event, but I received a compil
error. Would you mind giving me some sample code?

Thanks....A

--
Message posted from http://www.ExcelForum.com

AJ,

Here is some sample code

Private Sub Workbook_Open()

With Worksheets("Sheet1").ComboBox1
.AddItem "Item #1"
.AddItem "Item #2"
.AddItem "Item #3"
.AddItem "Item #4"
.AddItem "Item #5"
.AddItem "Item #6"
.ListIndex = 0
End With

End Sub

Make sure it is in the ThisWorkbook code module.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"AJ Master " wrote in message
...
Bob,

I added the code to the workbook open event, but I received a compile
error. Would you mind giving me some sample code?

Thanks....AJ


---
Message posted from http://www.ExcelForum.com/