Combo Box

[ndurrani]

I put a combo box or drop down box on the worksheet.
The source of the combo box is on different sheet. I set the range.
of the combo box to the source worksheet.

I set the cell link property to A1 cell. When I select any value fro
combo box, it shows number value, not the text in the combo box.

Another question, when you open the workbook, I want no pre-selecte
value in the combo box. I should be blank.

If anyone knows the solution, I will appreciate.

Thank

 

[jeff]

Hi,

For Q#1 - use the Index function in another cell
which refers to your linked cell. eg,=INDEX(B1:B6,E1)

jeff

 

[Dave Peterson]

Since you're getting a number back, you're using the DropDown from the Forms
Toolbar.

If you want the value to show up, you can either use the =index() solution that
Jeff suggested or change to the combobox from the Control Toolbox toolbar.

And you could use a small program that executes each time your workbook is
opened:

Option Explicit
Sub auto_open()

With Worksheets("sheet1")
'dropdown version
.DropDowns("drop down 1").ListIndex = 0
'combobox version
.ComboBox1.ListIndex = -1
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You may find using Data|Validation easier. If you want to check it out:
Debra Dalgleish has some tips for Data|validation at:
http://www.contextures.com/xlDataVal01.html

And you could clear a Data|Validation cell with code like:

Option Explicit
Sub auto_open()

With Worksheets("sheet1")
'datavalidation version
.range("a1").clearcontents
End With

End Sub