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.
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(), boolAllFalse As Boolean
'ReDim the arrays keeping departments and products at their maximum possible size:
ReDim arrDep(ActiveSheet.CheckBoxes.Count - 1): ReDim arrProd(ActiveSheet.CheckBoxes.Count - 1):
For Each chB In ActiveSheet.CheckBoxes 'iterate between check boxes:
If Mid(chB.Name, 9, 2) = "De" Then 'if a check box refers a department name:
If chB.Value = 1 Then 'if its value is True:
arrDep(iD) = chB.Name: iD = iD + 1 'put it in the departments array
End If
End If
If Mid(chB.Name, 9, 2) = "Pr" Then 'if a check box refers a product name:
If chB.Value = 1 Then 'if its value is True:
arrProd(iP) = chB.Name: iP = iP + 1 'put it in the products array
End If
End If
Next
If iD > 0 Then ReDim Preserve arrDep(iD - 1) 'redim the array preserving only the loaded elements
If iP > 0 Then ReDim Preserve arrProd(iP - 1) 'redim the array preserving only the loaded elements
Set cmb = ActiveSheet.OLEObjects("TransferList").Object 'set the combo to be loaded
cmb.Clear 'clear the combo items
boolAllFalse = onlyFalseChkB 'check if all check boxes value is False and place the result in a boolean var
For Each ws In Sheets 'iterate between all sehets
If boolAllFalse Then 'if all checkboxes value are False:
cmb.AddItem ws.Name 'add the sheet name in the combo
Else 'if not all check boxes value are False:
If iD > 0 Then 'if there are department check boxes in departments array:
mtch = Application.Match("CheckBox" & Mid(ws.Name, 9, 3), arrDep, 0) 'check if the sheet is found in the array
If Not IsError(mtch) Then 'if found
If cmb.ListCount > 0 Then 'if there are items in the combo
arrL = cmb.List 'extract the combo items in an array a 2D array with 10 columns (fastest way)
ReDim Preserve arrL(0 To cmb.ListCount - 1, 0 To 0) 'replace all (Null) values from columns 1 to 10)
mtch = Application.Match(ws.Name, arrL, 0) 'check if the sheet name is already added in the combo
If IsError(mtch) Then 'if not added:
cmb.AddItem ws.Name 'add it
End If
Else
cmb.AddItem ws.Name 'add the sheet name in the combo, if combo does not have any item (yet)
End If
End If
End If
'check products chkB:
If iP > 0 Then 'proceed in the same way for the products check boxes array:
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
End If
Next
End Sub
Function onlyFalseChkB() As Boolean
Dim chB As CheckBox
For Each chB In ActiveSheet.CheckBoxes
If chB.Value = 1 Then Exit Function
Next
onlyFalseChkB = True
End Function
In order to load the combo according to the above Sub rules when the sheet is activated, please copy the next code event in the sheet keeping the controls code module:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
LoadSheets_Combo
End Sub