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
Solved

ComboBox/Listbox Values Change based on Previously Set Variable

Posted on 2010-08-20
6
371 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: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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

828 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