Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    combo box and LinkedCell (XL97 sr2 on Win 2000 sp2)

    I'm using a combo box to include a drop down list on my sheet. It correctly display the text and feeds an associated number into the LinkedCell.

    All is well until I save and quit. When I reopen the sheet the value in the LinkedCell is displayed in the combo box instead of the text that was selected.

    Is this a 'feature' or am I missing something?

    stuck

  2. #2
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combo box and LinkedCell (XL97 sr2 on Win 2000 sp2)

    Can you post a sample that doesn't work?

    I tried this in both xl97 and xl2000 (on Win2k, SP2) and it worked fine. I have attached it for you to compare with your file. When I select an item from the drop down list (created by Data Validation feature), that value is retained when the workbook is re-opened next time (assuming of course I save the file).

    Deb <img src=/S/pinkelefant.gif border=0 alt=pinkelefant width=20 height=20>
    Attached Files Attached Files

  3. #3
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combo box and LinkedCell (XL97 sr2 on Win 2000 sp2)

    Thank you for your efforts but my drop down list is created using the combo box on the Control Toolbox toolbar not via Data Validation nor via the Drop down list available on the Forms toolbar. I didn't have the problem when I used the list on the Forms toolbar.

    Having read the Help file more closely it appears this is the way the LinkedCell of a combo box is supposed to work, i.e. my problem is as I suspected a 'feature'. I have come up with a work around but I can't post an example of the effect and my solution at the moment.

    Thanks again,

    (not) stuck

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combo box and LinkedCell (XL97 sr2 on Win 2000 sp2)

    The combobox from the control toolbox sends its selected item's <font color=red>value</font color=red> to the linked cell
    The combobox from the Forms Toolbar sends its selected item's <font color=red>index</font color=red> to the linked cell
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combo box and LinkedCell (XL97 sr2 on Win 2000 sp2)

    ...and the LinkedCell returns its value to the combobox. This from the ControlSource Property page of the XL97 VBA help file, my emphasis in red:

    Remarks

    The ControlSource property identifies a cell or field; it does not contain the data stored in the cell or field. If you change the Value of the control, the change is automatically reflected in the linked cell or field. <font color=red>Similarly, if you change the value of the linked cell or field, the change is automatically reflected in the Value of the control.</font color=red>

    Copyrightę 1996 Microsoft Corporation.

    I read that to mean that the act of saving was passing the value from the combobox to the LinkedCell one more time, hence the Linked Cell had changed and so the combobox took on the value of the LinkedCell. Thus when I reopen the file the combobox displays the value of the LinkedCell and not the text item I had originally selected.

    Or am I still missing the point?

    (confused rather than) stuck

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combo box and LinkedCell (XL97 sr2 on Win 2000 sp2)

    You are correct . Changing the value in the linked cell directly, causes the edit box of the dropdown to update to that value, NOT the value of the item that was previously selected. I guess anything that causes Excel to recalc will do that, including a save.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •