Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ComboBox/Listbox Values Change based on Previously Set Variable

Posted on 2010-08-20
6
Medium Priority
?
406 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
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: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

Independent Software Vendors: 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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

916 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