Label Controls Developer Tab

When you say cluttered, what do you mean?

You do know this is a public forum and I, along with anyone else who answers (or tries to answer) your questions are just other members of the public trying to help their fellow man?

If you want help from here, please help us to help you... :o)

What don't you get?  Did you follow the steps in my last post?  What happened?

Cheers

Rich

I'm not following you on the Active X Control whatsoever. No it makes worse sense and is cluttered.

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.