PDA

View Full Version : Combo box trouble in Excel



Mike S
02-05-2005, 10:31 AM
In EXCEL I’m trying to set up a combo box to select a value from a drop-down list. I’m following a tutorial taken off the web and it says that, having created the box I then right click and select ‘Format Control’. So far, so good - BUT the tutorial says I choose the ‘Control’ tab from the four presented (purportedly Size, Protection, Properties, Control): in my case the tabs presented are Size, Protection, Properties, Web. Then I cannot find a way of specifying the list itself for the Combo function to work with.

What am I doing wrong? What has the web to do with it? Should I be reading a different tutorial?

Our machine is P4 2.6 GHz, Windows XP Home, and Excel is part of Office 2000 installed.

odyssey
02-05-2005, 01:46 PM
This works in Excel 2002, but I'm unsure about 2000. Create a named range (e.g. test) of the items you want to list. Make sure you are in design mode if you are not already - to get into design mode bring up the visual basic toolbar and click on the setsquare and pencil icon. Then right click on the combo box and select properties. Find 'ListFillRange' in the properties and enter your named range (e.g. test). If you want to tie the result to a cell enter the cell reference under 'LinkedCell'. Click off design mode and check the dropdown list. Hopefully it should be all go.

Cheers
Dave

Parry
02-05-2005, 02:13 PM
In EXCEL I’m trying to set up a combo box to select a value from a drop-down list. I’m following a tutorial taken off the web and it says that, having created the box I then right click and select ‘Format Control’. So far, so good - BUT the tutorial says I choose the ‘Control’ tab from the four presented (purportedly Size, Protection, Properties, Control): in my case the tabs presented are Size, Protection, Properties, Web. Then I cannot find a way of specifying the list itself for the Combo function to work with.

What am I doing wrong? What has the web to do with it? Should I be reading a different tutorial?

Our machine is P4 2.6 GHz, Windows XP Home, and Excel is part of Office 2000 installed.

There are 2 sets of controls available - ActiveX and Forms. It sounds as though you have drawn a control from either the Visual Basic or Control Toolbox toolbar while the tutorial is talking about a control from the Forms toolbar. Forms controls rely upon values being presented to the sheet and formulas around these to determine results while ActiveX controls require code to be written when using them.

regards,
Graham

rad_s4
02-05-2005, 02:22 PM
If your Combobox is launched via VBA code, then it can be populated by a list of data when it is initialized.
In the VBA editor under the workbook Forms, right click on the Userform which contains the Combobox and select View Code.
As an example, the following code if copied there will populate the drop down with all the entries in columns A and B of the active worksheet (excluding the headers in A1:B1)

Private Sub UserForm_INITIALIZE()
ComboBox1.ColumnCount = 2
ComboBox1.ColumnWidths = 60
ComboBox1.List = Range("A2:B2", Range("A2:B2").End(xlDown)).Value
End Sub

HTH

Parry
02-05-2005, 02:52 PM
Hi rad_s4, I believe this is just a control on a sheet so there is no userform. :)

Mike S
02-05-2005, 05:43 PM
The tutorial I mentioned is at http://ferl.becta.org.uk/content_files/ferl/resources/schools/fairfax/lessonplan/plong/excellists/AdvancedExcelFunctions.ppt and ‘Parry’ has hit the nail on the head. I was selecting not from Forms but from Control Toolbox. We have a BFRI (Basic Failure to Read Instructions).

Thanks everyone for your help. My apologies for being a complete dummy 1st Class.

Parry
02-05-2005, 06:00 PM
No need to apologize. Its unusual that theres more than one set of controls and is a very common mistake to mix these up. :-)