Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dropdown 'Shapes' (97 SR2)

    Ahh, amatuer Excel Users...

    I have a user who created a huge spreadsheet which contains over 850 Dropdown Shapes. These dropdown shapes contain various items which the intended end user has selected.

    I'd like to build a macro to pull the data the user selects from the drop down and populate the cell directly beneath the dropdown. I've tried:

    ActiveSheet.Shapes("Drop Down 855").Value = Range("A1").Value

    but it doesn't like the syntax.

    An example is located here. Any ideas? I can't create true combo-boxes because the sheet has already been populated with the drop-downs.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dropdown 'Shapes' (97 SR2)

    I don't know if this is the same in Excel for the Mac, but in the Windows version, I can right-click a dropdown shape and select Format Control.

    In the dialog box that appears,the second item is Link to Cell. I can enter the address of a cell here.
    After clicking OK, the link is both ways: changing the selection in the dropdown shape changes the value in the linked cell, and vice versa.

    The value in the linked cell is not the same as the value in the dropdown shape, but the index in the list of entries. You can use VLookup to retrieve the value displayed in the dropdown shape.

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dropdown 'Shapes' (97 SR2)

    Thanks!

    Question, how might I apply vlookup? I can't say i've used that function before, and the help file is a little murky... [img]/forums/images/smilies/smile.gif[/img]
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dropdown 'Shapes' (97 SR2)

    <hr>and the help file is a little murky... <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <hr>
    .....ain't that the eternal truth.

    Until the cavalry arrives, does this and the attached thread help any?

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dropdown 'Shapes' (97 SR2)

    Unfortunately, No. It touches on the syntax somewhat, but doesnt turn greek into english quite yet...

    Thanks though!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

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

    Re: Dropdown 'Shapes' (97 SR2)

    Hi Hans,

    If the value of the dropdown is the index, then I guess one would use the INDEX function to extract the real value from the source list of the dropdown.

    For Form toolbar controls, the value is indeed the index.
    But for the Control Toolbox controls, the value is the selected item, not the index and no formula is needed..
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Dropdown 'Shapes' (97 SR2)

    Close:

    Range("A1").Value=ActiveSheet.Shapes("Drop Down 855").Value
    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
  •