Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Dec 2003
    Location
    London, England
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking Drop Down Menu's (2003)

    Need some help on the attached.
    Basically I need to link to drop down menu's together with the first one determining what is shown in the second.
    If anyone needs to send me an attachment that is too large for this forum please email me at carl290666@hotmail.com

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linking Drop Down Menu's (2003)

    See the thread beginning with <post#=384591>post 384591</post#> for a method of doing this.

  3. #3
    Lounger
    Join Date
    Dec 2003
    Location
    London, England
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Drop Down Menu's (2003)

    I have no experience of VBA or writing Macro's
    I looked at Hans' code in the country spreadsheet that he put together in response tho the earlier post.
    Would the code I need be similar ? Step be step what exactly do I need to do to get my spreadsheet working ?

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

    Re: Linking Drop Down Menu's (2003)

    See the attached workbook. I did the following:
    - I assigned a name to each of the ranges that will act as list fill range for the second combo box.
    - I set the linked cell for the second combo box to B29 - it was the same as for the first combo box; this leads to confusion.
    - I created a macro, and assigned it to the first combo box.

    The macro looks at B20, the cell linked to the first combo box, and sets the list fill range for the second combo box accordingly:

    Sub Combo1_Click()
    ' change list fill range
    Select Case Range("B20")
    Case 1
    ActiveSheet.Shapes("Drop Down 2").ControlFormat.ListFillRange = "BT_Tariff"
    Case 2
    ActiveSheet.Shapes("Drop Down 2").ControlFormat.ListFillRange = "Cable_Tariff"
    Case 3
    ActiveSheet.Shapes("Drop Down 2").ControlFormat.ListFillRange = "Colt_Tariff"
    Case 4
    ActiveSheet.Shapes("Drop Down 2").ControlFormat.ListFillRange = "MCI_Tariff"
    End Select
    ' clear current selection
    ActiveSheet.Shapes("Drop Down 2").ControlFormat.Value = 0
    End Sub

  5. #5
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linking Drop Down Menu's (2003)

    For a method that does not use VBA see the attached file.

    It is based on using a named formula to calculate the input range for the second combo box. An explanation of how it works is given in the file.

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Drop Down Menu's (2003)

    We strongly recommend to all Loungers that they do not include private e-mail addresses in their posts - as such addresses can be harvested by a spam robot. The e-mail addresses in your profiles are protected. If you need to do this in future, I suggest that you refer people to the e-mail address listed in your profile. You may also wish to edit your existing posting to incorporate the same change there. HTH
    Gre

Posting Permissions

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