Excel Dependent User Form Dropdown List Won't Update-CodePudding

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!

CodePudding user response：

LMFAAAOOOOOOOOOOOOOOO I just re-made the macro in a new excel file and it works fine. I guess we will never know why it didn't work.

CodePudding user response：

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