Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Dropdown lists (XP)

    Im creating this spreadsheet. It has several drodown menus with the info on another page.
    However, the information for one of the dropdowns is very long, about 300 to 500 records. It contains 3 sections.
    If there anyway I can, for instance, tick a box and then one of the sections appears, so I don't have to scroll through 300 records. I have the print area set, so can always outside this area create a trigger or so.

    Thanks

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dropdown lists (XP)

    This is theoretical and entirely untested. But to start off with, the long list will have to be broken into 3 lists, one for each section. (Or a filter can be used to dynamically filter for a section.) Then I would suggest a second dropdown that lists the name of each section. (Not a tick box as this can only be ON/OFF!) When you select the section to view from the first dropdown, it will link to (or filter) the list containing records for that category. The second dropdown can then show that set of records. Macros can also be used to automate the actions to populate the list. Remember that you can link macros to the dropdown controls on the forms toolbar too!
    Regards,
    Rudi

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Dropdown lists (XP)

    We were thinking amongst the same line. I have added the first drop down box with the 3 groups. Not too sure how I link the second box to the first one ?? With an IF statement ??

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

    Re: Dropdown lists (XP)

    See the thrread starting at <post:=429,797>post 429,797</post:> for several examples.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Dropdown lists (XP)

    I used the following
    Private Sub ComboBox1_Change()
    With ComboBox2
    Select Case ComboBox1.Text
    Case "Product1": .ListFillRange = "a1:a20"
    Case "Product2": .ListFillRange = "b1:b20"
    Case "Product3": .ListFillRange = "c1:c20"
    End Select
    End With
    End Sub

    The range is on a sheet called "products" Do I have to add that to the range ? It doesn't work with the above

    Thanks again

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

    Re: Dropdown lists (XP)

    Try

    Case "Product1": .ListFillRange = "products!a1:a20"

    etc.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Dropdown lists (XP)

    I can't get it to work. Shame.
    I added the macro, called all the product ranges, but it only shows the first range

    Its probably too hot even for excel [img]/forums/images/smilies/smile.gif[/img]

    Thanks

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

    Re: Dropdown lists (XP)

    You can post a stripped down copy of your workbook, if you like.

  9. #9
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Dropdown lists (XP)

    I had to really minimise the information and had to strip the macro out of it because of the size.
    The cell references are A and b 7

    Thanks so much

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

    Re: Dropdown lists (XP)

    Umm, your attachment hasn't made it...

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dropdown lists (XP)

    <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> - I think Karat made the file toooooo small! Karat, the file must be more than 0KB in size!
    Regards,
    Rudi

  12. #12
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Dropdown lists (XP)

    As I said before its too hot and too late, 12 hours ahead of you guys.. Lets try again.
    Attaching a file does help *sigh*
    Sorry guys.. a senior moment from paradise

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

    Re: Dropdown lists (XP)

    You have used Data | Validation, so there are no actual combo box objects on your worksheet. You can use the Worksheet_Change event (in the module belonging to the worksheet) to change the formula for the validation in column B when the user selects a product in column A. I have added the code in the attached workbook. I couldn't determined which data were for product3, so it uses the same list as product2.

    This is the code. You can inspect it by right-clicking the sheet tab of the Company file sheet and selecting View code from the popup menu.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    ' Did user change a cell in A7:A11?
    If Not Intersect(Target, Range("A7:A11")) Is Nothing Then
    ' If so, turn off events temporarily
    Application.EnableEvents = False
    ' Loop through the modified cells
    For Each oCell In Intersect(Target, Range("A7:A11"))
    With oCell.Offset(0, 1)
    ' Clear the cell to the right
    .ClearContents
    ' And set the Validation formula to refer to the product
    .Validation.Modify Formula1:="=" & oCell.Value
    End With
    Next oCell
    ' Turn on events again
    Application.EnableEvents = True
    End If
    End Sub

  14. #14
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Dropdown lists (XP)

    Wow you guys are so good
    Thanks Hans

  15. #15
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dropdown lists (XP)

    >>>> Wow you guys are so good - REPHRASE - Hans is BRILLIANT!
    Regards,
    Rudi

Page 1 of 2 12 LastLast

Posting Permissions

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