Excel Dependent User Form Dropdown List Won't Update

Question

I want to create 2 dependent dropdown lists (form contorl) with VBA:

Listadesplegable1: The first dropdown list selects between 4 different cases. Listadesplegable5: The second dropdown list selects the different options of each case.

Here's the code:

Option Explicit
Sub Listadesplegable1_Cambiar()
Dim num As Single
Dim i, j As Integer
Dim k, m As Integer
Dim condicion1, condicion2, condicion3 As Boolean

num = Worksheets("I|O").Range("C13")

condicion1 = True
condicion2 = True
condicion3 = True

i = 1
j = 1

k = 7
m = 7

With Worksheets("I|O").DropDowns("Lista desplegable 5")
    .ListFillRange = " "
    Select Case num
        Case 1
            Do While condicion1
                .List(i) = Worksheets("Perfiles H ICHA 2001").Cells(k, 1).Value
               
                If Worksheets("Perfiles H ICHA 2001").Cells(k + 1, 1) = 0 Then
                    condicion1 = False
                End If
                i = i + 1
                k = k + 1
            Loop
        Case 2
            Do While condicion2
                .List(j) = Worksheets("Perfiles H AISC").Cells(m, 1).Value

                If Worksheets("Perfiles H AISC").Cells(m + 1, 1) = 0 Then
                    condicion2 = False
                End If
                j = j + 1
                m = m + 1
            Loop
        Case 3
            .ListFillRange = "Especial"
 
        Case 4
            Do While condicion3
                .List(i) = Worksheets("Perfiles H ICHA 2008").Cells(k, 1).Value
               
                If Worksheets("Perfiles H ICHA 2008").Cells(k + 1, 1) = 0 Then
                    condicion3 = False
                End If
                i = i + 1
                k = k + 1
            Loop
    End Select
End With
End Sub

Because im using Excel in Spanish Language "Sub Listadesplegable1_Cambiar()" is the same as Change() of Dropdown List. I don't really know if that's an issue.

The problem is that when i change the first dropdown list, the second doesn't update... The macro works fine on .xls but not in .xlsm

Thanks!

Note that `Dim i, j As Integer` declares `j As Integer` but `i As Variant`. In VBA you need to specify a type for **every** variable, otherwise it is `Variant` by default: `Dim i As Integer, j As Integer`. Further I recommend to use `Long` instead of `Integer` because Excel has more rows than `Integer` can handle. — Pᴇʜ, Feb 04 '22 at 13:37
Oh Thanks! Im new to VBA and didn't know that. Changed it and defined i,j,m,k in different lines as Long. But it still doesn't work.. I have to click the "run" button on the VBA window to update it everytime.. Don't know how to fix this of how to solve this problem with a different approach. — maxipoblete, Feb 04 '22 at 13:57
Firstly, you should change `.ListFillRange = " "` with `.ListFillRange = ""`. Secondly, you should try placing a break point on `Select Case num` code line. Select it and press `F9`. Then run the code line by line, using `F8` and move the cursor to each involved variable to see its value. Are you sure that `...Cells(k + 1, 1) = 0` is what you want checking? If an empty string (nothing in cell), you should use `""` instead of `0`. — FaneDuru, Feb 04 '22 at 14:17
@FaneDuru Thanks! I followed your advice and changed those things. I checked with F8 and the variables and its values seem to be okay. In fact, the 2nd dropdown list populates with the options of the selected case in the 1st dropdown list (4 cases).. But only works the first time. If i change the case on the 1st dropdown list, the 2nd dropdown doesn't update. Any other suggestions? Thanks! — maxipoblete, Feb 04 '22 at 14:36
I am afraid, I cannot get you. The above code is not first drop down selection dependent, from what I am able to understand. If you do not change anything in your sheet, it will return the same for ever... Can you better clarify what you try accomplishing and what are your expectations when run it second time? Do you change anything in the code? If not, what do you expect from it and it does not match your expectations? What changing "the case" should mean? If `num` variable, do what I recommended before and see what the code does for that specific `Case`. What is `"Especial"`, for instance? — FaneDuru, Feb 04 '22 at 14:50
@FaneDuru Oh okay im sorry, here I go again: Let's say I have 4 different cases, which are listed on the first dropdown list. Each case has different options, which should be displayed on a second dropdown list. Maybe it's easier if we imagine in terms of "Countries" for the first dropdown list, and "Cities" for the second dropdown list right? Its important to mention that for each "Country" case, the "Cities" are listed on different sheets tabs. What im trying to achieve is actually very simple. Whenever I change the "Country" case, I want the 2nd list to display the corresponding "Cities" — maxipoblete, Feb 04 '22 at 15:00
I will leave my office in some minutes. I still insist to do what I suggested above. Please, visually see, what the code does in that specific case! I asked you what `"Especial"` is... Is it a named range? It looks that the code works, but your conditions are not correctly configured. I mean, what you expect to be returned, does not exist in the range your code processes. At least, this is what I think based on you I can see and what you are saying... — FaneDuru, Feb 04 '22 at 15:04
@FaneDuru This sheet is for my structural engineering internship and it's about steel beam types, the four "cases" are 4 different standards that specify the dimensions of the steel beam. Then, for example, for the case 1 "ICHA 2001", there are like 800+ different steel beams, that are completely different than those of case 2 "AISC 13th Edition". For the "Especial" case i only want the second dropdown list to have the string "Especial", just that. — maxipoblete, Feb 04 '22 at 15:09
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/241731/discussion-between-maxipoblete-and-faneduru). — maxipoblete, Feb 04 '22 at 15:10

score 1 · Answer 1 · answered Feb 04 '22 at 16:11

Option Explicit

Sub Listadesplegable1_Change()

    Dim num As Long, ws As Worksheet, k As Long
    num = Me.Range("C13")
    
    With Me.Listadesplegable5
        .Clear
        Select Case num
            Case 1
                Set ws = Sheets("Perfiles H ICHA 2001")
            Case 2
                Set ws = Sheets("Perfiles H AISC")
            Case 3
                .AddItem "Especial"
                Exit Sub
            Case 4
                Set ws = Sheets("Perfiles H ICHA 2008")
        End Select
                 
        k = 7
        Do While ws.Cells(k, 1).Value <> ""
            .AddItem ws.Cells(k, 1).Value
            k = k + 1
        Loop
    End With
End Sub

Excel Dependent User Form Dropdown List Won't Update

1 Answers1