Label Controls Developer Tab

The activex List Box control works in a similar way.

Do the same steps, but this time insert a List Box (ActivX Control).

When you click Properties, a Properties window shows.

Look about halfway down that window, type A1:A5 next to ListFillRange.  Type D2 (must be empty) next to LinkedCell.

You'll notice the Design Mode button on the ribbon is pressed.  Click it to un-press it and exit design mode.  The control can now be used rather than edited.

Notice now how when you click an option in the ActivX ListBox, D2 (the linked cell) displays the value (text), not the position of the selection.  Just like the Form Control, if you change the cell to a value matching one of the list items, that item shows as selected.

Remember you could have the itemlist, and the linked cell in a hidden cell, or on another sheet (which could also be hidden).

You asked what is the purpose of linking cells like this.  Well if all you want from your form is to record a user's input, not need.  But if you want something to happen in Excel depending on a user's input then this is how you communicate the user's input to the rest of Excel, you formulas, etc.

You could even have a second list box that has list items that vary depending on the selection in the first list box...

Make sense?

Cheers

Rich

I see whats happening when I did it but not clear on this purpose of linking cells like this. Is this process good for every form control and not the active x controls?

In http://office.microsoft.com/client/helppreview.aspx?AssetId=HP102366819990&lcid=2057&NS=EXCEL&Version=12&respos=0&CTT=1&queryid=fcbeaf0d%2De677%2D405a%2Db069%2D323ecc5d2be0, look under Add a list box (Form control), step 4 talks about the Cell Link.

Here are some step-by-step instructions to insert a List Box (Form Control) on a worksheet:

1. Click Developer tab > Control group > Insert > Form Controls > ListBox (Form Control)
2. Draw it on your worksheet
3. With the new control still selected, click Developer tab > Control group > Properties button.  The Format Control dialog opens.
4. In the Control tab, click the top Input Range button (on the right of the white box) and select a range, e.g. Sheet1!A1:A5
5. Do the same for the Cell link input, this time select a single cell, e.g. cell D1
6. Click OK.
7. Go to cell A1, enter "Fred", in A2 enter "Bill", in A3 enter "Bob", in A4 enter "Jane" and in A5 enter "John".

As you did that, you should have seen each entry appear in your list box.

If you click on the second entry in the list box, you should see the number 2 appear in cell D1 (the Linked Cell).

You can now use that value in another formula.  For example, in cell E1 enter

=IF(D1=2,"Fred scored the highest mark in his Excel exam","Fred didn't get the top mark")

Make sense?

Cheers

Rich

PS. Note also, in my above example, that if you change the value of D1 (the linked cell), the corresponding entry in the list box is selected, e.g. change D1 to 3, Bob will be selected in the list box.

Where would I be looking in this link you gave? No it does not make sense. There is no linking here.

Take a look at http://office.microsoft.com/client/helppreview.aspx?AssetId=HP102366819990&lcid=2057&NS=EXCEL&Version=12&respos=0&CTT=1&queryid=fcbeaf0d%2De677%2D405a%2Db069%2D323ecc5d2be0

Basically, insert a control, show the properties by clicking the Properties button in the Developer tab, select a cell to be linked to then what ever is input into the control should appear in the linked cell.

Make sense?

Cheers

Rich