ComboBox Dependencies

[8700Rakf]

Hello! I am totally new within the VBA universe, so i hope you guys can help me out.

So I want to create a ComboBox dropdown, where I have a few selection options availaible. So depending on what is picked on ComboBox1 a certain selection option is shown in ComboBox2.

Here is the code use so fare.

Sub ComboBox1_Change()

Me.ComboBox1.Clear


Dim Version1 As String
Dim Version2 As String
Dim Version3 As String
Dim Version4 As String


Version1 = "OIML R51 1996 (Old EU)"
Version2 = "OIML R51 2006 (New EU)"
Version3 = "NMIA (AU)"
Version4 = "NIST (US)"



Me.ComboBox1.AddItem Version1
Me.ComboBox1.AddItem Version2
Me.ComboBox1.AddItem Version3
Me.ComboBox1.AddItem Version4


If CombBox1 = Version1 Then
    Me.ComboBox2.AddItem "Y(a)"


End If


End Sub

Another issue is, that when I go out of Design mode, and try it out, I can see the selection availaible in ComboBox1, but it doesnt stay in the box when selecting it. The ComboBox Remains Blank.

I am totally new, so it is clear that i am doing something wrong.

 

[dmt32]

Hi,
Welcome to the forum.

Every time you make a selection your combobox clears itself which suggests that you have your code to populate in in wrong event.

I take it that your combobox is an activex control on your worksheet & if so, you could as a suggestion, place the code to populate it in another event like worksheet activate. (Populates when you select the worksheet)

example

 Private Sub Worksheet_Activate()    
    Dim Versions As Variant
    
    Versions = Array("OIML R51 1996 (Old EU)", "OIML R51 2006 (New EU)", "NMIA (AU)", "NIST (US)")
    
     With Me.ComboBox1
        .Clear
        .List = Versions
     End With


End Sub

I have updated code to use an array which populates combobox via its List property – this negates need for all the Additem calls.

You would then use the change event to populate combobox2 based on combobox1 selection

Sub ComboBox1_Change()
    'code to populate combobox2 here


End Sub

Dave

 

[8700Rakf]

The first part makes alot of sense, but I am in a but of a doubt, when populating CB2.

How would you populate correctly, when f.eks.

with

OIML R51 1996 (Old EU) i want CB2 to be populated with A

and with

OIML R51 2006 (New EU) i want CB2 to populate A and B

and also have the clearing option.



By the way. Thanks for the quick response earlier, that was very helpful.

 

[dmt32]

Hi,
publish all the selection options for CB2 & I or another here should be able to guide you

Dave

 

[8700Rakf]

Hi,
publish all the selection options for CB2 & I or another here should be able to guide you

Dave

I think i made it work by using Cases, but i am not sure thats the proper way to do it.

 

[dmt32]

I think i made it work by using Cases, but i am not sure thats the proper way to do it.

If it works for you then that can be regarded as a proper way but there nearly always is a cleaner method

here is another possibly

Sub ComboBox1_Change()
    Dim SelectionIndex As Integer
    Dim Dependentlist As Variant
    
    Dependentlist = Array(Array("A"), _
                          Array("A", "B"), _
                          Array("A", "B", "C"), _
                          Array("A", "B", "C", "D"))
    
    SelectionIndex = Me.ComboBox1.ListIndex
    With Me.ComboBox2
        .Clear
        .Enabled = CBool(SelectionIndex <> -1)
    If .Enabled Then .List = Dependentlist(SelectionIndex) Else Me.ComboBox1.Activate
    End With
End Sub

Change the values (shown in RED) in the arrays to meet your project need.

Dave

 

[8700Rakf]

If it works for you then that can be regarded as a proper way but there nearly always is a cleaner method

here is another possibly

Sub ComboBox1_Change()
    Dim SelectionIndex As Integer
    Dim Dependentlist As Variant
    
    Dependentlist = Array(Array("A"), _
                          Array("A", "B"), _
                          Array("A", "B", "C"), _
                          Array("A", "B", "C", "D"))
    
    SelectionIndex = Me.ComboBox1.ListIndex
    With Me.ComboBox2
        .Clear
        .Enabled = CBool(SelectionIndex <> -1)
    If .Enabled Then .List = Dependentlist(SelectionIndex) Else Me.ComboBox1.Activate
    End With
End Sub

Change the values (shown in RED) in the arrays to meet your project need.

Dave

Thank you so Much! That made it work all the better!