Updating different combo boxes simultaneously

    I have a source combo box (cboRegions) that contains the names of various regions.


    Each region has its own radios.


    I want a second combobox (cboCrews) to populate it's contents with a different range name depending upon the cboRegions value.


    Each region has a range name associated with it (e.g. "Northwest"--"northwestradios"; "Southwest"--"southwestradios",etc.).


    I will have to use a Select Case statement because I have several selections.


    So, if cboRegions.value = "Northwest"


    Select Case cboRegions.value

    case "Northwest"
    cboCrews.(value?rowsource?controlsource?) = northwestradios


    What object do I use after cboCrews to link it to northwestradios- range, valuer, rowsource, controlsource?



    Thanks

    Hi,


    Below You find procedures that shows an example how to do it:


    Option Explicit


    Private Sub ComboBox1_Change()
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim rnWeek1 As Range, rnWeek2 As Range
    Dim vaWeek1 As Variant, vaWeek2 As Variant


    Set wbBook = ThisWorkbook
    Set wsSheet = wbBook.Worksheets("Blad1")


    With wsSheet
    Set rnWeek1 = .Range("Week1")
    Set rnWeek2 = .Range("Week2")
    End With


    vaWeek1 = rnWeek1.Value
    vaWeek2 = rnWeek2.Value


    With ComboBox2
    .Clear
    Select Case ComboBox1.Value
    Case "Week 1"
    .List = vaWeek1
    Case "Week 2"
    .List = vaWeek2
    End Select
    .ListIndex = -1
    End With


    End Sub


    Private Sub CommandButton1_Click()
    Unload Me
    End Sub


    Private Sub UserForm_Initialize()
    Dim vaOptions As Variant


    vaOptions = VBA.Array("Week 1", "Week 2")


    With Me.ComboBox1
    .Clear
    .List = vaOptions
    .ListIndex = -1
    End With


    End Sub



    Here You can download the workbook

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login