Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ComboBox/Listbox Values Change based on Previously Set Variable

Posted on 2010-08-20
6
Medium Priority
?
411 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
  • 3
  • 2
6 Comments
 
LVL 24

Expert Comment

by:Tracy
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 24

Expert Comment

by:Tracy
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:
Tracy earned 1000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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 …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

572 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