如何利用勾選方塊來篩選資料 勾選方塊篩選

Jared

想請問各位大大
我現在想要利用表單來篩選資料

下載 (67.91 KB)

2015-6-8 16:43

下拉式選擇資料還可以執行
但如果想要利用勾選資料的方式
不曉得問題點在哪裡 ><
測試.zip (18.57 KB)

下載次數: 8

2015-6-8 16:44

1. Dim a, b, c, d, e, f, g, h, i, j, k, l, m
   
2. Private Sub CommandButton1_Click() '篩選條件
   
3. 
   
4. a = Test.ComboBox1.Value '季節
   
5. b = Test.ComboBox2.Value '月份
   
6. 
   
7. d = CheckBox1.Value
   
8. e = CheckBox2.Value
   
9. f = CheckBox3.Value
   
10. g = CheckBox4.Value
    
11. h = CheckBox5.Value
    
12. i = CheckBox6.Value
    
13. j = CheckBox7.Value
    
14. k = CheckBox8.Value
    
15. l = CheckBox9.Value
    
16. m = CheckBox10.Value
    
17. 
    
18. c = Array(d, e, f, g, h, i, j, k, l, m)
    
19. 
    
20. 
    
21. With ActiveSheet.Range("$A$1:$K$121")
    
22.     .Parent.AutoFilterMode = False   '顯示全部資料 ->新的 多重篩選
    
23.    
    
24.     '多重篩選  ..........
    
25.     If a <> "" Then .AutoFilter Field:=1, Criteria1:=a
    
26.     If b <> "" Then .AutoFilter Field:=2, Criteria1:=b
    
27.     If c <> "" Then .AutoFilter Field:=3, Criteria1:=c, Operator:=xlFilterValues
    
28.     '多重篩選  ..........
    
29. End With
    
30. End Sub
    
31. 
    
32. Private Sub CommandButton2_Click() '清除內容
    
33. Me.ComboBox1 = ""
    
34. Me.ComboBox2 = ""
    
35. Me.CheckBox1 = ""
    
36. Me.CheckBox2 = ""
    
37. Me.CheckBox3 = ""
    
38. Me.CheckBox4 = ""
    
39. Me.CheckBox5 = ""
    
40. Me.CheckBox6 = ""
    
41. Me.CheckBox7 = ""
    
42. Me.CheckBox8 = ""
    
43. Me.CheckBox9 = ""
    
44. Me.CheckBox10 = ""
    
45. End Sub
    
46. 
    
47. Private Sub CommandButton3_Click() '取消
    
48. Test.Hide
    
49. End Sub
    
50. 
    
51. Private Sub CommandButton4_Click() '展開
    
52. ActiveSheet.Range("$A$1:$K$121").AutoFilter Field:=1
    
53. ActiveSheet.Range("$A$1:$K$121").AutoFilter Field:=2
    
54. ActiveSheet.Range("$A$1:$K$121").AutoFilter Field:=3
    
55. End Sub
    
56. Private Sub UserForm_Initialize()
    
57. 
    
58.     ComboBox1.List = Array("Q1", "Q2", "Q3", "Q4") '季
    
59.    
    
60.     ComboBox2.List = Array("一月", "二月", "三月", "四月", "五月", "六月", "七月", "八月", "九月", "十月", "十一月", "十二月") '月
    
61. 
    
62. End Sub

複製代碼

Jared

回復 2# luhpro


    謝謝大大 :)