Help Dependent User Form Dropdown List Won't Update

[maxipoblete]

Hello,
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

"Sub Listadesplegable1_Cambiar()" is just in the Change() of Dropdownlist1 in spanish btw.

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!

 

[shknbk2]

I tried saving a file as xls and then creating two dropdowns with a simple Change macro for the first.

Sub DropDown1_Change()
    Dim i As Integer
    With ActiveSheet.DropDowns("Drop Down 2")
        .ListFillRange = " "
        For i = 1 To 4
            .List(i) = i + Range("D1").Value
        Next
    End With
End Sub

I didn't have any problems in the xls file or re-saving it as xlsm.

Are you familiar with the VBA debugging process? What happens if you put a breakpoint on the "num = Worksheets("I|O").Range("C13")" line (put cursor on that line and hit F9) and make a change selection in that dropdown? Does code execution start? What happens as you step through the code (F8)?

 

[maxipoblete]

I tried saving a file as xls and then creating two dropdowns with a simple Change macro for the first.

View attachment 57028 View attachment 57029

Sub DropDown1_Change()
    Dim i As Integer
    With ActiveSheet.DropDowns("Drop Down 2")
        .ListFillRange = " "
        For i = 1 To 4
            .List(i) = i + Range("D1").Value
        Next
    End With
End Sub

I didn't have any problems in the xls file or re-saving it as xlsm.

Are you familiar with the VBA debugging process? What happens if you put a breakpoint on the "num = Worksheets("I|O").Range("C13")" line (put cursor on that line and hit F9) and make a change selection in that dropdown? Does code execution start? What happens as you step through the code (F8)?

I started with a new excel file from scratch , with almost the same code and everything worked fine. I checked with F8 and the values seem fine too. But i have one big problem. Whenever I save, close, and re-open the workbook, the second dropdown list linked cell has the index of the last selected item, but the dropdown list appears empty, until i click on it and "refreshes". Here's the code:

Option Explicit

Sub Listadesplegable1_Cambiar()

Dim num As Single

Dim i As Long
Dim j As Long
Dim k As Long
Dim m As Long

Dim condicion1 As Boolean
Dim condicion2 As Boolean
Dim 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 = " "
    Worksheets("I|O").Cells(44, 3).Value = ""
    
    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) = "" 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) = "" Then
                    condicion2 = False
                End If
                j = j + 1
                m = m + 1
            Loop
           
        Case 3
            .List(1) = "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) = "" Then
                    condicion3 = False
                End If
                i = i + 1
                k = k + 1
            Loop
    End Select
End With
End Sub

 

[shknbk2]

When you re-open the workbook, how do you want the second dropdown to behave? Is the empty list okay while having the previous entry is bad? There could be a workbook_open event that empties the value in the second dropdown.

 

[maxipoblete]

Thanks for replying. Maybe some pictures will help:

Here you can see the second dropdown with an option selected. C44 is the linked cell.

If i save the sheet and re-open it again look what happens:

The linked cell C44 is with the same index (202) , and yeah of course the dropdown isn't empty, it's actually with the previous selection, thats what I want, thats okay. But for some reason it "appears" empty to the eye.. Unless you click again on the dropdown:

There we go.. it seems that it refreshed and now i can see again the selection

do you get me? I guess its a bug of the 365 version... I opened the same sheet with other computer with Excel 2016 and I didn't have this "bug".. ): what do i do?