Here they are.
1st clears all
2nd fills CB's
3rd gets desired data
Code:
Private Sub cmdReset_Click()
'clear the data
cmbProducts.Clear
cmbCustomerType.Clear
cmbRegion.Clear
With Sheets("View")
.Visible = True
.Range("dataSet").CurrentRegion.Offset(1).ClearContents
End With
End Sub
Private Sub cmdUpdateDropDowns_Click()
sn = Sheets("data").Range("C3:E472")
For j = 1 To 3
With CreateObject("System.Collections.ArrayList")
For jj = 1 To UBound(sn, 1)
If sn(jj, j) <> vbNullString Then
If Not .Contains(CStr(sn(jj, j))) Then .Add CStr((sn(jj, j)))
End If
Next
.Sort
Choose(j, cmbProducts, cmbRegion, cmbCustomerType).List = Application.Transpose(.toarray)
End With
Next
End Sub
Private Sub cmdShowData_Click()
Dim rngTarget As Range, i As Integer
Application.ScreenUpdating = False
Sheets("View").Range("dataSet").CurrentRegion.Offset(1).ClearContents
With Worksheets("data")
Set rngTarget = .Range("A1:J" & .Cells(Rows.Count, 1).End(xlUp).Row)
With rngTarget
For i = 1 To 3
.AutoFilter i + 2, Choose(i, cmbProducts.Text, cmbRegion.Text, cmbCustomerType.Text)
Next
.Offset(1).SpecialCells(xlCellTypeVisible).Copy Sheets("View").Range("B13")
.AutoFilter
End With
End With
Application.ScreenUpdating = True
End Sub
Bookmarks