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' choose
LoadSheets_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...