list box in Excel

[worsthandleever]

how can I put a list box in Excel? Looking for macro/VBA code -- just not sure how to proceed.

 

[acron]

Do you want the Listbox on the worksheet or as part of a userform. What sort of data doe wish the Listbox to include, and what/where is the source for this data. ?

A.C.

 

[worsthandleever]

want the list box to be part of a worksheet and deriving it's values from cells w/in the worksheet e.g. A1-A5 so that the list box would have list the values of A1-A5.

 

[SkipVought]

Hi,

1. Activate the Control Toolbox (left click the toolbar)

2. Click on ListBox and place on sheet

3. Assign ListFillRange to the Named Range or absolute range.

4. Assign whatever other properties you wish.

5. Doulbl click the ListBox to activate the VB editor for the ListBox1_Click event.

6. Assign whatever code you wish.

7. Ext the Design Mode.

Now your box is ready to make a selection

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[uberpudge]

You could use the "Validation" feature under the "Data" menu.
Set the "Allow" dropdown to "List" and the "Source" to A1-A15, also make sure that "In-cell dropdown" is checked.

One caveat, the source must be on the same sheet as the validated cell.

Alternately, you could use a combo box on the Forms toolbar or the combobox on the Control Toolbox toolbar.

For the Forms toolbar combox, draw it and set the "Input range" to A1-A15 and put the output cell address in "Cell link".

For the Control Toolbox combo box, you could try the following code to populate it:

Dim lCell as Range

For Each lCell In Sheet1.Range("A1", "A15&quot
ComboBox1.AddItem (Conversion.CStr(lCell.Value))
Next

Hope this helps,
Pete

 

[SkipVought]

No, No, No...

Wrong Caveat, uberpudge. A named range will work from ANY sheet.

The use of Forms as opposed to Control Toolbox is an inferior approch in most cases. The functionality and controls available with acixvex controls, is far superior to MS Forms, a less sophistacated feature of an earlier version of Excel. Skip,
metzgsk@voughtaircraft.com

 

[uberpudge]

Skip,

Maybe in later versions of MS Excel, but in Excel 97 if I try to link it to another sheet ('Sheet2'!$A$1:$A$15) I get "'Sheet2'!$A$1:$A$15" as the only choice in the dropdown.

For Excel 97, validation must be done using cells on the same sheet.

Hope this helps,
Pete

 

[SkipVought]

Sorry, uberpudge,

I just tested it on Excel 97.

Works for List and Combo both with a named range on another sheet. Skip,
metzgsk@voughtaircraft.com

 

[uberpudge]

I think I have not been clear.

Using "Validation" under the "Data" menu requires the range to be on the same sheet as the validated cell.

Using an actual combobox, Forms or Active X (Control Toolbox), does not impose this limitation.

A Forms combobox can have any range.
An Active X combobox is not even limited to a range.

A "Validated" cell using an "In-cell dropdown" to achieve the impression/illusion of a proper dropdown list requires a range on the same sheet as the validated cell.

Please accept my humble apology for not being clear.

Hope this helps,
Pete

 

[uberpudge]

Skip,

I apologize.

Just to be sure, I checked the Help on Data Validation. I got the following:

If the list of valid entries is in another worksheet or workbook, define a name for the external data on the active worksheet. In the Source box, you can then refer to the name on the same worksheet. For example, if the data you want to use in a formula is in cells A6:A12 on the first worksheet in a workbook named Budget.xls, you could define the name ValidData on the active worksheet to be =[Budget.xls]Sheet1!$A$6:$A$12 and then enter =ValidData in the Source box.

So the caveat does not apply if you define a named range on the current sheet as the external sheet or workbook range.

Hope this helps,
Pete