¦p¦ó§Q¥Î¤Ä¿ï¤è¶ô¨Ó¿z¿ï¸ê®Æ ¤Ä¿ï¤è¶ô¿z¿ï

Jared

·Q½Ð°Ý¦U¦ì¤j¤j
§Ú²{¦b·Q­n§Q¥Îªí³æ¨Ó¿z¿ï¸ê®Æ

¤U¸ü (67.91 KB)

2015-6-8 16:43

¤U©Ô¦¡¿ï¾Ü¸ê®ÆÁÙ¥i¥H°õ¦æ
¦ý¦pªG·Q­n§Q¥Î¤Ä¿ï¸ê®Æªº¤è¦¡
¤£¾å±o°ÝÃDÂI¦b­þ¸Ì ><
´ú¸Õ.zip (18.57 KB)

¤U¸ü¦¸¼Æ: 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() '¿z¿ï±ø¥ó
   
3. 
   
4. a = Test.ComboBox1.Value '©u¸`
   
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   'Åã¥Ü¥þ³¡¸ê®Æ ->·sªº ¦h­«¿z¿ï
    
23.    
    
24.     '¦h­«¿z¿ï  ..........
    
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.     '¦h­«¿z¿ï  ..........
    
29. End With
    
30. End Sub
    
31. 
    
32. Private Sub CommandButton2_Click() '²M°£¤º®e
    
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() '®i¶}
    
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") '©u
    
59.    
    
60.     ComboBox2.List = Array("¤@¤ë", "¤G¤ë", "¤T¤ë", "¥|¤ë", "¤­¤ë", "¤»¤ë", "¤C¤ë", "¤K¤ë", "¤E¤ë", "¤Q¤ë", "¤Q¤@¤ë", "¤Q¤G¤ë") '¤ë
    
61. 
    
62. End Sub

½Æ»s¥N½X

Jared

¦^´_ 2# luhpro


    ÁÂÁ¤j¤j :)

luhpro

·Q½Ð°Ý¦U¦ì¤j¤j
§Ú²{¦b·Q­n§Q¥Îªí³æ¨Ó¿z¿ï¸ê®Æ
¤U©Ô¦¡¿ï¾Ü¸ê®ÆÁÙ¥i¥H°õ¦æ
¦ý¦pªG·Q­n§Q¥Î¤Ä¿ï¸ê®Æªº¤è ...
Jared µoªí©ó 2015-6-8 16:44

¬Ý°_¨Ó¦Û°Ê¿z¿ï¦P¤@Äæ¦n¹³¤£¯à¦P®Éº¡¨¬¶W¹L¤@­Ó±ø¥ó. (§Y¤£¯à¦b¦P¤@Ä椤¦P®ÉÅã¥Ü¥X¨Ó¤pµØ»P¤p©ú)

©Ò¥H§Ú³o¸Ì±Ä¥Î³v¦C¿z¿ï,
¥¼¯à¦P®Éº¡¨¬©Ò¦³±ø¥óªº¦C«h±N¨äÁôÂÃ,
¯d¤Uªº´N¬O§Ú­Ì­nªº¸ê®Æ¤F.

¤]¦]¬°±Ä¥Îªº¬OÁôÂæCªº¤è¦¡,
©Ò¥H "«ö¶s1" ¦ì¸m­n¤W²¾¨ì ²Ä 1 ¦C ¥H§K³QÁôÂæӵLªkÂI¿ï.

­º¥ý, ¦]À³µ{¦¡½Õ¾ã, ¥þ°ìÅܼƪº«Å§i¤º®e¤]°µ½Õ¾ã:

1. Dim a, b, s, lRow

½Æ»s¥N½X

"¿z¿ï±ø¥ó" «ö¶sµ{¦¡­×§ï¦p¤U:

1. Private Sub CommandButton1_Click() '¿z¿ï±ø¥ó
   
2. 
   
3. ActiveSheet.Rows.Hidden = False
   
4. 
   
5. a = Test.ComboBox1.Value '©u¸`
   
6. b = Test.ComboBox2.Value '¤ë¥÷
   
7. 
   
8. s = ""
   
9. s = s & IIf(CheckBox1, CheckBox1.Caption & ",", "")
   
10. s = s & IIf(CheckBox2, CheckBox2.Caption & ",", "")
    
11. s = s & IIf(CheckBox3, CheckBox3.Caption & ",", "")
    
12. s = s & IIf(CheckBox4, CheckBox4.Caption & ",", "")
    
13. s = s & IIf(CheckBox5, CheckBox5.Caption & ",", "")
    
14. s = s & IIf(CheckBox6, CheckBox6.Caption & ",", "")
    
15. s = s & IIf(CheckBox7, CheckBox7.Caption & ",", "")
    
16. s = s & IIf(CheckBox8, CheckBox8.Caption & ",", "")
    
17. s = s & IIf(CheckBox9, CheckBox9.Caption & ",", "")
    
18. s = s & IIf(CheckBox10, CheckBox10.Caption & ",", "")
    
19. 
    
20. lRow = 2
    
21. With ActiveSheet
    
22.   While .Cells(lRow, 1) <> ""
    
23.     If Not (.Cells(lRow, 1) = a And .Cells(lRow, 2) = b And _
    
24.         InStr(1, s, .Cells(lRow, 3)) <> 0) Then .Rows(lRow).Hidden = True
    
25.     lRow = lRow + 1
    
26.   Wend
    
27. End With
    
28. End Sub

½Æ»s¥N½X

"²M°£¤º®e" «ö¶sµ{¦¡¤¤,
CheckBox ªº­È¦b¦¹¤£¯à³]¬° "" ¦ÓÀ³³]¬° False ©Î true ,
¥t¥~¬JµM¿z¿ïªº±ø¥ó¤w¸g³Q²M°£¤F,
¿z¿ïµ²ªG¦ÛµMÀ³¸Ó¤@¨Ö°µÁÙ­ì.
¬G­×§ï¦p¤U:

1. Private Sub CommandButton2_Click() '²M°£¤º®e
   
2. Me.ComboBox1 = ""
   
3. Me.ComboBox2 = ""
   
4. Me.CheckBox1 = False
   
5. Me.CheckBox2 = False
   
6. Me.CheckBox3 = False
   
7. Me.CheckBox4 = False
   
8. Me.CheckBox5 = False
   
9. Me.CheckBox6 = False
   
10. Me.CheckBox7 = False
    
11. Me.CheckBox8 = False
    
12. Me.CheckBox9 = False
    
13. Me.CheckBox10 = False
    
14. ActiveSheet.Rows.Hidden = False
    
15. End Sub

½Æ»s¥N½X

"®i¶}" «ö¶sµ{¦¡¤]­n¸òµÛ°µ½Õ¾ã:

1. Private Sub CommandButton4_Click() '®i¶}
   
2.   ActiveSheet.Rows.Hidden = False
   
3. End Sub

½Æ»s¥N½X

¥t¥~§AªºÀÉ®×  ComboBox2.List ¤º®e¨Ã¤£¯à»P B Äæ¹ïÀ³, (¨Ò¦p : "¤@" ¤£¯à¹ïÀ³ "¤@¤ë"),
¦P®É¥i¥H¦Ò¼{¤@¨Ö³£½á»Pªì­È,
©Ò¥H§Ú°µ¤F¥H¤Uªº­×§ï:

1. Private Sub UserForm_Initialize()
   
2. 
   
3.     With ComboBox1
   
4.       .List = Array("Q1", "Q2", "Q3", "Q4") '©u
   
5.       .Value = "Q1"
   
6.     End With
   
7.     With ComboBox2
   
8.       .List = Array("¤@", "¤G", "¤T") '¤ë
   
9.       .Value = "¤@"
   
10.     End With
    
11. End Sub

½Æ»s¥N½X

³Ì«á Q1 ¤£·|¦³ "¤@","¤G","¤T" ¥H¥~ªº¤ë¥÷,
¬G¥i¥H¦bÅܧó ComboBox1 ¤º®e®É¤@¨Ö§â ComboBox2 ªº¿ï³æ½Õ¾ã¦n:

1. Private Sub ComboBox1_Change()
   
2.   Select Case ComboBox1
   
3.   
   
4.     Case "Q1"
   
5.       ComboBox2.List = Array("¤@", "¤G", "¤T") '¤ë
   
6.    
   
7.     Case "Q2"
   
8.       ComboBox2.List = Array("¥|", "¤­", "¤»")  '¤ë
   
9.    
   
10.     Case "Q3"
    
11.       ComboBox2.List = Array("¤C", "¤K", "¤E") '¤ë
    
12.    
    
13.     Case "Q4"
    
14.       ComboBox2.List = Array("¤Q", "¤Q¤@", "¤Q¤G")  '¤ë
    
15.   End Select
    
16.   
    
17. End Sub

½Æ»s¥N½X

·íµM, µ{¦¡ÁÙ¦³Â²¤ÆªºªÅ¶¡,
¥B CheckBox ¤]¥i¥H¦Ò¼{°µª«¥ó°}¦C,
¤£¹L¦]¬°µ{¦¡¨Ã¤£½ÆÂø,
©Ò¥H³o³¡¥÷§Ú´N¥ý¤£¦Ò¼{¤F.
´ú¸Õ-a.zip (18.13 KB)

¤U¸ü¦¸¼Æ: 12

2015-6-11 23:35