Solved

ComboBox/Listbox Values Change based on Previously Set Variable

Posted on 2010-08-20
6
389 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

632 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