Solved

ComboBox/Listbox Values Change based on Previously Set Variable

Posted on 2010-08-20
6
377 Views
Last Modified: 2012-05-10
I need help creating a ComboBox/Listbox that populates different selection values based on a previously selected Variable. ie. If the Variable above selected is a 2 then only retrun row Sorce of Values!A5:A9.  But if the Previous Variable is a 5 then Return Rowsoruce Values!A16:A32. Thanks!
0
Comment
Question by:bank_on_it
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 24

Expert Comment

by:broomee9
ID: 33486557
Try this, see attached example.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$F$2" Then
        If Range("F2").Value = 2 Then
            ComboBox1.ListFillRange = "A5:A9"
        ElseIf Range("F2").Value = 5 Then
            ComboBox1.ListFillRange = "A16:A32"
        Else
        End If
    Else
    End If
End Sub

Open in new window

Book1.xls
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33486561
you didn't mention how the variable is set, so I'll set my own
sub button1_click()
dim var as string

var = inputbox("Enter 2 or 5.")
if var = 2 then
    for each c in range("A5:A9")
             listbox1.items.add c.value
    next
else
       if var = 5 then
            for each c in range("A16:A32")
                    listbox1.items.add c.value
            next
       else
                exit sub
       end if
end if
end sub
0
 
LVL 6

Author Comment

by:bank_on_it
ID: 33486794
The Variable is set by the Previous ComboBox.  I'm setting the Higher level then I want to restrict choices to only the valid Values from the 2nd comboBox.. Here's kindov what I've been working with. Disclamer I'm new to VB. This is in a Userform as well.

Code:
Private Sub ComboBox3_Click()
If ComboBox2.Value = "XXA" Then
    ComboBox3.ListFillRange = Values!M4: M11
Else: ComboBox2.Value = "XXB"
  ComboBox3.ListFillRange = Values!M12: M17
End If
End Sub
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Expert Comment

by:broomee9
ID: 33486831
Try changing to this, because on the change of combobox2, you want to change combobox3:

Private Sub ComboBox2_Change()
If ComboBox2.Value = "XXA" Then
    ComboBox3.ListFillRange = "Values!M4: M11"
Else: ComboBox2.Value = "XXB"
  ComboBox3.ListFillRange = "Values!M12: M17"
End If
End Sub
0
 
LVL 24

Accepted Solution

by:
broomee9 earned 250 total points
ID: 33486838
Actually, I would make one more change:

Private Sub ComboBox2_Change()
If ComboBox2.Value = "XXA" Then
    ComboBox3.ListFillRange = "Values!M4: M11"
Elseif ComboBox2.Value = "XXB"
  ComboBox3.ListFillRange = "Values!M12: M17"
Else
End If
End Sub
0
 
LVL 6

Author Closing Comment

by:bank_on_it
ID: 33486852
Pefect! Thanks so much!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question