Combo Box initial set up

[AJ Master]

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

 

[Bob Phillips]

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]

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

 

[Tom Ogilvy]

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

End Sub

 

[Soo Cheon Jheong]

Hi,

Sub TEST() 'ComboBox_Initialize()

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

End Sub


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

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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]

Thanks much, Bob!

It worked like a charm!