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?


  • 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
    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
    .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!