Excel ActiveX Combo Box not Showing Selection-CodePudding

I'm using a ActiveX Combo Box to show all or some Worksheets. In addition to that, on the same worksheet I have some Form Controls Check Boxes where the user can use them as filters for the Combo Box. So for instance, each check box have the name of department and so when one is checked the list will be updated with the worksheets related to that name. Which works fine.

However, the problem I have is that if I do choose an option from the Combo Box drop down list, it doesn't come up on the field of the Combo Box.

Here is the code I'm using at the moment.

Private Sub TransferList_DropButtonClick()
    Application.EnableEvents = False
    Dim ws As Worksheet

    I = 1
    TransferList.Clear
    For Each ws In Sheets
        If ActiveSheet.Shapes("CheckBox_Viva").ControlFormat.Value = 1 Then
            TransferList.AddItem ws.Name
            I = I   1
        End If
    Next ws
    
    Application.EnableEvents = True

End Sub

I did some research and I did find that by using the TransferList_Change the issue is resolved but the filtering is not working (no change whether a check box is True or False).

What am I missing?

Cheers.

CodePudding user response：

Like I said in my comment I will leave in some minutes. Please, try understanding the next way of working and extrapolate it at your situation. If something unclear, do not hesitate to ask. But I will be able to answer only after some hours, when I will be at home.

Open a new workbook and save it as 'xlxm`, to accept macros.
Place on the working sheet a combo box (ActiveX type) and so many Form Type check boxes as workbook number of sheets. Name them (name and caption) exactly as the sheets, or in a way to make them matching one or some more sheets. Name the combo as "TransferList".
Copy the next code in a Standard module:

Sub LoadSheets_Combo()
     Dim ws As Worksheet, cmb As MSForms.ComboBox
     Set cmb = ActiveSheet.OLEObjects("TransferList").Object
     cmb.Clear
     For Each ws In Sheets
            If ActiveSheet.Shapes(ws.Name).ControlFormat.Value = 1 Then
                cmb.AddItem ws.Name
            End If
     Next
End Sub

Right click on each check box and choose Assign macro... and choose 'Maros in: This workbookand at 'Macro name' chooseLoadSheets_Combo`.
Start paying with check box values and see how the combo is loaded, only with the sheets matching (somehow) with the ticked check boxes.

Test the above suggested scenario and send some feedback...

Edited:

Please, try the next code able to do what (I understood) you need for your case:

Option Explicit

Sub LoadSheets_Combo()
     Dim ws As Worksheet, cmb As MSForms.ComboBox, strDep As String, strProd As String, arrDep, arrProd
     Dim chB As CheckBox, iD As Long, iP As Long, mtch, arrL()
     ReDim arrDep(ActiveSheet.CheckBoxes.Count - 1): ReDim arrProd(ActiveSheet.CheckBoxes.Count - 1):
     For Each chB In ActiveSheet.CheckBoxes
        If Mid(chB.Name, 9, 2) = "De" Then
            If chB.Value = 1 Then
                arrDep(iD) = chB.Name: iD = iD   1
            End If
        End If
        If Mid(chB.Name, 9, 2) = "Pr" Then
            If chB.Value = 1 Then
                arrProd(iP) = chB.Name: iP = iP   1
            End If
        End If
     Next
     If iD > 0 Then ReDim Preserve arrDep(iD - 1)
     If iP > 0 Then ReDim Preserve arrProd(iP - 1)
     Set cmb = ActiveSheet.OLEObjects("TransferList").Object
     cmb.Clear
     For Each ws In Sheets
         If iD > 0 Then
            mtch = Application.Match("CheckBox" & Mid(ws.Name, 9, 3), arrDep, 0)
            If Not IsError(mtch) Then
                If cmb.ListCount > 0 Then
                    arrL = cmb.List
                    ReDim Preserve arrL(0 To cmb.ListCount - 1, 0 To 0)
                    mtch = Application.Match(ws.Name, arrL, 0)
                    If IsError(mtch) Then
                        cmb.AddItem ws.Name
                    End If
                Else
                    cmb.AddItem ws.Name
                End If
            End If
        End If
        'check products chkB:
         If iP > 0 Then
            mtch = Application.Match("CheckBox" & Right(ws.Name, 3), arrProd, 0)
            If Not IsError(mtch) Then
                If cmb.ListCount > 0 Then
                    arrL = cmb.List
                    ReDim Preserve arrL(0 To cmb.ListCount - 1, 0 To 0)
                    mtch = Application.Match(ws.Name, arrL, 0)
                    If IsError(mtch) Then
                        cmb.AddItem ws.Name
                    End If
                Else
                    cmb.AddItem ws.Name
                End If
            End If
        End If
     Next
End Sub

Not time now to comment it. I worked on somebody else laptop...