list box help

  • Thread starter Thread starter Fan924
  • Start date Start date
F

Fan924

I am using a list box from the forms toolbar. This is new for me. I
loaded 10 numbers of 4 digits. The output is numbers 1 to 10. I would
rather it output the 4 digit numbers instead. It looks like this is
not an option. Are there any good links on the list box? I have found
many of the bad ones.
 
Don't use a CellLink (assuming that is what you are currently doing);
rather, assign the ListBox to use this macro instead...

Sub AssignListItemToCell()
With Worksheets("Sheet1").Shapes("List Box 1").OLEFormat.Object
Range("A1").Value = .List(.ListIndex)
End With
End Sub

Change the worksheet reference ("Sheet1" in my example code), List Box name
("List Box 1" in my example code) and the receiving Cell (A1 in my example
code) to suit your actual conditions.
 
Here is one place:

http://office.microsoft.com/en-us/excel/HP102366811033.aspx#1

It sounds like you were returning the list index number instead of the list
index value. Also, you might find the ListBox from the Control Toolbox more
flexible to work with, since you can access all of the properties through a
Properties dialog box. VBA help files also cotain more information on the
OLEObject actveX ListBox than the ones from the Forms toolbar.
 
You could replace the Listbox from the Forms toolbar with a listbox from the
Control toolbox toolbar.

That will have the behavior that you want (the linked cell will show the
displayed choice).

But you could use the listbox from the Forms toolbar and a couple of cells--one
cell for the link and one cell showing the value...

For instance, if your list is on a sheet (say Sheet99 in A1:A10) and your linked
cell is on sheet1 in A1, you could use:

=if(a1=0,"",index(sheet99!a1:a10,a1))

The linked cell returns an index (1 to how many entries) into the listbox's
list.

I don't know of any links for the listbox, good or bad.
 
Back
Top