Combo Box in Word or Excel 6

[randy700]

I need to create a Word document or an Excel spreadsheet that has several combo boxes. I can find the procedures for placing the combo box on the document/spreadsheet but don't know how to populate the combo box. I need this to obtain information that will eventually be imported into an Access database.

Any thoughts?

Randy

 

[rdeleon]

I'm not as adept in Word, but in Excel you have 2 options.

Data\Validation, Allow: List Type in your combo values in the Source: box with commas separating your values. This is a good method if your combo values are static.

If you place a combobox object from the VB tools menu, you will need to use VBA (Alt-F11) to fill the combo box using the AddItem method. A little more complicated for static combos, but great for linking to other list sources, such as another excel spreadsheet.

Rene'

 

[dcompto]

See

http://support.microsoft.com/default.aspx?scid=kb;en-us;161598&Product=wrd20

Microsoft Knowledge Base Article - 161598
OFF: How to Add Data to a ComboBox or ListBox in Excel or Word

 

[fumei]

First off (and this is only a Word reply - not Excel, which may be different), are you talking about a FormField dropdown; or are you talking about an ActiveX control combobox?

If it is a FormField dropdown:

1. right click the formfield
2. select Properties
3. type in the items in the Drop-down item field
4. press Enter (or click Add)

You can move the items up and down the list

If it is an ActiveX control combobox, the list is populated by code, such as:

Private Sub AddToComboBox()
ComboBox1.AddItem "one"
ComboBox1.AddItem "two"
End Sub



Gerry

 

[randy700]

Thank you. All answers have been very helpful.

Randy

 

[BLBurden]

Thank you very much Fumei, this posting was very helpful to me, and I gave you a star

BLB - Always Grateful
A good friend will bail you out of jail. A true friend will be sitting at your side saying, "Boy - didn't we have fun?"

 

[fumei]

Well I am glad it helped. Should add another point.

If it is a FormField dropdown, I should have also mentioned that, it too may be populated by code.

ActiveDocument.FormFields("formfieldName"). _
DropDown.ListEntires.Add item

will add an item to the dropdown list.

To clear ALL items, in cases you need to repopulate the whole thing:

ActiveDocument.FormFields("formfieldName"). _
DropDown.ListEntires.Clear

Gerry

 

[TimGoff]

Hi Gerry,

Think we've spoken before...

I would like to use ActiveX combo boxes in Excel - with values that will remain constant - ie once added they will not change - and have to be populated dynamically from a db etc

Would you know how to do this?

 

[fumei]

Sub Whatever()
combobox1.additem item
etc.
End sub

populates the combobox. The displayed item is the ListIndex property. If you want, after you populate the box, to display the first item in the list, put:

combobox1.ListIndex = 0

at the end of the AddItem list. The index is 0 based, so this displays item #1.

The issue is simply getting the values from the DB to AddItem to the combobox. You could retrieve the field data one at a time; or you could something like:

Dim var
dim i as Integer (unless you have LOTS of records!)
For var = 1 to {i]number of records to use[/i]
comboxbox1.additem record#i
i = i + 1
next

Gerry

 

[TimGoff]

Great cheers Gerry.

I also have one other combo box where a user can choose week numbers - obviously they are not going to change. Is there a way of effectively embedding the values within the combobox so they don't have to be loaded from a list?

Thanks
Tim

 

[fumei]

If it is an ActiveX control...no. The population is loaded by code. By design a control is empty. It has to have items added to it. What is the problem really? You only have to make the list once.

Gerry

 

[TimGoff]

A user would select a week number from a drop down - that is then passed to code...

Maybe a formfield would be better?

 

[fumei]

A user would select a week number from a drop down - that is then passed to code...

Maybe a FormField would be better?

I am not seeing the issue here. A user selects a week number (although I am not sure what a week number is...), and...what? Yes, you can have it pass to code. Either with an ActiveX_Change event, or yes, you certainly could use a FormField dropdown, and then have the code that runs from the result of the dropdown start as an OnExit macro.

The OnExit macro could check for the dropdown item selected and then...do whatever it is you want to do. What is the issue?

Again, what is the issue here?

Gerry

 

[TimGoff]

Week number: the number of a specific week in the year, therefore 3rd Jan would be in Week 1.

Combo box: Would like it pre-populated with the week numbers- trying to avoid having to populate it using a on click event...therefore was thinking form fields.

I can't find a way of referencing a Combo box form field in the same way you can in Word ...ActiveDocument.FormFields etc

Is that any clearer?

 

[fumei]

I take it you are talking about a combobox from the Forms toolbar, but in Excel rather than Word.

What version of Excel are you using? It makes a big difference.

Later versions do not really encourage the use of controls created by the Forms toolbar. They encourage the use of ActiveX controls from the Controls Toolbox.

Forms toolbar controls get their item lists from cells. This is a huge difference from Word, where you input the dropdown items. Now Help states that you can edit text, but this only works for controls that have a caption, like a check box.

To refer to the Forms control, I think you need to refer to the Shapes collection, but frankly, I thought the Word object model has its oddities, but Excel! Yikes. I find it very strange indeed. I think I will leave this to the Excel gurus. But you are right, there is no FormField collection that I can find in Excel.

Gerry