[µo°Ý] checkbox&optionbutton­È°ÝÃD

ayubbs

¤£¦n·N«ä¡A¦]¦b¯¸¤º·j´M¼Æ¤p®É¡A¹ê¦b§ä¤£¨ìuserform¤Uªºcheckboxªº­Èªº§ì¨ú
¨ìtextbox¤¤ªº¤å³¹¡A¥i¯à¤p©f²Â©å¡A¥u¦n¦A¦V¦U¦ì¥ý¶i½Ð±Ð¥Îªk

¦]¦Û¤w´ú¸Õ®Évba½X®M¥Îªº«Ü¶ÃÀɮפ¤§Y¥¼¦³ªþ¤W¡A·Q½Ð¤j®a«ü¾É¥Îªk
ª©¤W§äªº¨ì¤å³¹¦h¬°sheet¤UÀ³¥Î¡A¦ý¤p©f·Q±N¦¹¥\¯à®M¥Î©óuserform¤U
ªºtextbox¤U¡A·Q½Ð´N«ü¾É¦p¦ó§ì¨ú¿ï¾Ü«áªº­Èªº¤è¦¡¡C

ÁÂÁ¤j®a¯uªº«Ü¤£¦n·N«ä¡A¤]¤£°÷¥Î¤ß¡A¾Ç¤F«Ü¤[¤@ª½¦³«Ü¦hªº¦a¤è·d¤£À´

q1.rar (18 KB)

¤U¸ü¦¸¼Æ: 5

2014-12-16 01:14

¤U¸ü (5.27 KB)

2014-12-16 01:13

1.¬°ÂI¿ï«á¤â°Ê¿é¤J¡A²M°£
2.¬°ÂI¿ï«á¦Û°Ê¥[¤J
3.¬°ÂI¿ï«á¤â°Ê¥[¤J¡A²M°£
4.¬°ÂI¿ï«á¦Û°Ê·s¼W

stillfish00

¦^´_ 1# ayubbs
1.

1. Private Sub CommandButton1_Click()
   
2.   Dim s As String, i As Long
   
3.   For i = 1 To 4
   
4.     With Controls("CheckBox" & i)
   
5.       If .Value Then
   
6.         s = IIf(s = "", .Caption, s & ";" & .Caption)
   
7.       End If
   
8.     End With
   
9.   Next
   
10.   TextBox1.Value = s
    
11. End Sub
    
12. 
    
13. Private Sub CommandButton2_Click()
    
14.   TextBox1.Value = ""
    
15. End Sub

½Æ»s¥N½X

2.

1. Private Sub OptionButton1_Click()
   
2.   TextBox2 = OptionButton1.Caption
   
3. End Sub
   
4. Private Sub OptionButton2_Click()
   
5.   TextBox2 = OptionButton2.Caption
   
6. End Sub
   
7. Private Sub OptionButton3_Click()
   
8.   TextBox2 = OptionButton3.Caption
   
9. End Sub

½Æ»s¥N½X

3.

1. Private Sub CommandButton3_Click()
   
2.   Dim i As Long
   
3.   For i = 4 To 6
   
4.     With Controls("OptionButton" & i)
   
5.       If .Value Then
   
6.         TextBox3.Value = .Caption
   
7.         Exit For
   
8.       End If
   
9.     End With
   
10.   Next
    
11. End Sub
    
12. Private Sub CommandButton4_Click()
    
13.   TextBox3.Value = ""
    
14. End Sub

½Æ»s¥N½X

4.

1. Private Sub CheckBox5_Click()
   
2.   UpdateTextBox4
   
3. End Sub
   
4. Private Sub CheckBox6_Click()
   
5.   UpdateTextBox4
   
6. End Sub
   
7. Private Sub CheckBox7_Click()
   
8.   UpdateTextBox4
   
9. End Sub
   
10. Private Sub CheckBox8_Click()
    
11.   UpdateTextBox4
    
12. End Sub
    
13. 
    
14. Private Sub UpdateTextBox4()
    
15.   Dim s As String, i As Long
    
16.   For i = 5 To 8
    
17.     With Controls("CheckBox" & i)
    
18.       If .Value Then
    
19.         s = IIf(s = "", .Caption, s & ";" & .Caption)
    
20.       End If
    
21.     End With
    
22.   Next
    
23.   TextBox4.Value = s
    
24. End Sub

½Æ»s¥N½X

P.S.  
ÂI¿ïcheckbox¦³­ÓÄÝ©Ê¥sGroupName
ABC À³¸Ó³]¸m¦P¤@­ÓGroupName
¤j¤¤¤p ³]¸m¥t¤@­ÓGroupName

ayubbs

¦^´_ 2# stillfish00


    ÁÂÁ¸ѵª¸Ñ¨M¤F¨Ï¥Î´b
¥i§_½Ð±Ð²Ä¥|¨Ò³o¤G¥y»yªkªº·N«ä¬°·Q¦h¤F¸ÑÀ³¥Î

If .Value Then
        s = IIf(s = "", .Caption, s & ";" & .Caption)  


¥t¦³´ú¸Õ¤@ªk ÂI¿ï§¹«á¦Û°ÊÃö³¬userform

1. Private Sub CheckBox5_Click()
   
2.   UpdateTextBox4
   
3. userform2.hide
   
4. End Sub

½Æ»s¥N½X

¦pªG¦³¤@°ïªºCheckBox¥u¯à¨Ï¥Î¤â°Ê¤è¦¡¤@­Ó¤@­Ó¼W¥[

ÁÂÁÂ

GBKEE

¦^´_ 3# ayubbs
¤@°ïªºCheckBox¥i¥Îª«¥óÃþ§O¼Ò²Õ,¼g¤U¦@¥Îªºµ{§Ç

ayubbs

¦^´_ 2# stillfish00


    ¤j¤j¥i§_¦A½Ð±Ð¡A¦b´ú¸ÕTEXTBOX¤¤¦pªG¨S¿é¤J­È®É¡A§ì¨ú¸ÓLABLEªº­È¡A¦ý§ìªº®É­Ô³º¥u¨úªº
    ¨ìLABLE.1ªº±±¨î¶µªº¦WºÙ¤w¨Ï¥Î¤F.CAPTION¥i¬O´NµLªk¨ú±o­È¡A·Q¦A¦V¤j¤j½Ð±Ð¥i¬O»yªk¨º¸Ì
¤£¥¿½T

¤U¸ü (13.55 KB)

2014-12-23 11:25

¥tªþÀɤ¤¦³¥t¤@COMBOBOXªº°ÝÃD¡ACOMBOBOX¬O§_¥i¦³¹³LISTBOX¤@¼Ëªº¿ï¨ú¸ê®Æ«á¥i±N¸Ó
¦C¨ä¥¦¸ê®Æ¾É¥X¦ÜTEXTBOX¤¤¤Î¶i¦æ­×§ïOR§R°£ªº°Ê§@

¤U¸ü (12.5 KB)

2014-12-23 11:28

ÁÂÁ¤£¦n·N«ä¤S´£¥X¤@°ï°ÝÃD¡A³Ìªñ«ÜµÛ°g¾Ç²ß±±¨î¶µ¡AÁÂÁ¤j®a

lable.rar (15.28 KB)

¤U¸ü¦¸¼Æ: 4

2014-12-23 11:29

ayubbs

¦^´_ 4# GBKEE


ÁÂÁÂG¤j¡A¤p©fªº¥\¤OÁÙ¤£°÷¼g¥Xª«¥óÃþ§O¼Ò²Õ¡A·|«ùÄò¥[ªo¦hª¦¤åºN¯Á½m²ß¤¤

¤U¸ü (18.49 KB)

2014-12-23 11:35

¹³¹Ï¤¤«Ø¥ß¸s²Õ«á¹ï±±¨î¶µ¦³¤°»ò¯S§O¥Î·N¡A¤£´N¤£¯à­Ó§O¨Ï¥Î¤F¶Ü?

stillfish00

¦^´_ 5# ayubbs
¨£ªþÀÉ¡AÁÙ¦³¬OLabel¤£¬OLable
lable.zip (15.79 KB)

¤U¸ü¦¸¼Æ: 6

2014-12-27 10:55

la

ayubbs

¦^´_ 7# stillfish00


    ÁÂÁ¤j¤jªº«ü¾É¡A¥t©ócombobox¨Ï¥Î¤¤¡A¦p²M³æ­ì©l¬°ªÅ®É¡AµLªk°õ¦æ
    ¦pªí³æ¤@¶}©l¹w«Ø¥ß®É³£·|¬°µL¸ê®Æ®É´NAÄ欰ªÅ¡A»Ý­×§ï¨º³¡¥÷¤£³y¦¨¤£
   ¯à§PŪ¡A¥t°õ¦æ§R°£«ácombobox.list ¨Ã¤£·|§ó·s¡A¤w§R°£¸ê®ÆÁÙ¬O«O¯d¦b
   list¤¤¡A¥BaÄæ¯à­­¨î¤£¥i¿é¤J¬Û¦Pªº¸ê®Æ¦p aaa ¤w¦s¦b½Ð­«·s¿é¤J

ÁÂÁª©¤j   
§R°£¸ê®Æ«á¨S¦³²MªÅcombox¤Îtextbox­È¡A§Ú©ó§R°£°Ê§@«á¥[¤W combbox1 = "" ©ó§R°£«á¥i²MªÅ­È¡A¦ý;¤´
¥¼¸Ñ¨Mlist§ó·s°ÝÃD¡C
Private Sub CommandButton1_Click()
  Dim oFind As Object
  
  With Sheets("Sheet1")
    Set oFind = .Range("A:A").Find(what:=ComboBox1.Value, LookIn:=xlValues, lookat:=xlWhole)
    oFind.EntireRow.Delete
    ComboBox1.Value = ""  End With
End Sub
label ¸ò lable ¯u¬O©_§® ^^ ÁÂÁª©¤j¤£¶Þ«ü¾É

GBKEE

¦^´_ 6# ayubbs

UserForm2

1. Option Explicit
   
2. Dim Rng As Range
   
3. Private Sub UserForm_Initialize()
   
4.     Set Rng = ThisWorkbook.Worksheets("Sheet1").Range("A1")
   
5.     With ComboBox1   '
   
6.         .Value = ""
   
7.         .ColumnCount = 1
   
8.         If Rng <> "" Then
   
9.             .List = Rng.CurrentRegion.Value
   
10.             .Value = Rng
    
11.         Else
    
12.             .Clear
    
13.         End If
    
14.     End With
    
15. End Sub
    
16. Private Sub ComboBox1_Change()
    
17.     Dim AR, i As Integer
    
18.     AR = Array(TextBox1, TextBox2, TextBox3)
    
19.     With ComboBox1
    
20.         If .ListIndex > -1 Then
    
21.             For i = 0 To UBound(AR)
    
22.                 AR(i).Value = .List(.ListIndex, i + 1)
    
23.             Next
    
24.             '¤@°ïªºªº±±¨î¶µ¼g¤£§¹
    
25.            ' TextBox1 = .List(.ListIndex, 1)
    
26.            ' TextBox2 = .List(.ListIndex, 2)
    
27.            ' TextBox3 = .List(.ListIndex, 3)
    
28.         End If
    
29.     End With
    
30. End Sub
    
31. Private Sub CommandButton1_Click() '§R°£¿ï¾Ü¦C
    
32.     With ComboBox1
    
33.         If .ListIndex > -1 Then
    
34.             If MsgBox("§R°£¿ï¾Ü¦C" & .Value, vbYesNo) = vbYes Then
    
35.                 Rng.CurrentRegion.Rows(.ListIndex + 1).Delete
    
36.                 Set Rng = ThisWorkbook.Worksheets("Sheet1").Range("A1")
    
37.                 If Rng <> "" Then
    
38.                     .List = Rng.CurrentRegion.Value
    
39.                 Else
    
40.                     .Clear
    
41.                 End If
    
42.             End If
    
43.         Else
    
44.             MsgBox "¸ê®Æ®w ¨S¦³ " & .Value, , "§R°£¿ï¾Ü¦C"
    
45.         End If
    
46.     End With
    
47. End Sub
    
48. Private Sub CommandButton2_Click() '·s¼W¤@¦C
    
49.     Dim AR
    
50.     AR = Array(ComboBox1, TextBox1, TextBox2, TextBox3)
    
51.     With ComboBox1
    
52.         If .ListIndex > -1 Then
    
53.             MsgBox "¸ê®Æ®w¤¤¤w¦³ " & .Value, , "·s¼W¤@¦C"
    
54.         ElseIf .ListIndex = -1 Then
    
55.             If InStr("," & Join(AR, ",") & ",", ",,") Then
    
56.                 MsgBox "¸ê®Æ¤£¥þ"
    
57.             ElseIf MsgBox("·s¼W¤@¦C " & .Value, vbYesNo) = vbYes Then
    
58.                 If Rng = "" Then
    
59.                      Rng.Resize(, UBound(AR) + 1).Value = AR
    
60.                 Else
    
61.                     With Rng.CurrentRegion
    
62.                         .Cells(.Rows.Count + 1, 1).Resize(, UBound(AR) + 1) = AR
    
63.                     End With
    
64.                 End If
    
65.                 .List = Rng.CurrentRegion.Value
    
66.             End If
    
67.         End If
    
68.     End With
    
69. End Sub
    
70. Private Sub CommandButton3_Click()  '­×§ïÀx¦s
    
71.     Dim Ar1, Ar2
    
72.     With ComboBox1
    
73.         If .ListIndex > -1 Then
    
74.             Ar1 = Array(ComboBox1, TextBox1, TextBox2, TextBox3)
    
75.             Ar2 = Application.Transpose(Application.Transpose(Rng.CurrentRegion.Rows(.ListIndex + 1)))
    
76.             If InStr("," & Join(Ar1, ",") & ",", ",,") Then
    
77.                 MsgBox "¸ê®Æ¤£¥þ !!"
    
78.             ElseIf Join(Ar1, ",") = Join(Ar2, ",") Then
    
79.                 MsgBox Join(Ar1, ",") & vbLf & "¸ê®Æ¨S¦³­×§ï!!"
    
80.             ElseIf MsgBox("­×§ïÀx¦s " & vbLf & Join(Ar1, ","), vbYesNo) = vbYes Then
    
81.                 Rng.CurrentRegion.Rows(.ListIndex + 1).Resize(, UBound(Ar1) + 1) = Ar1
    
82.                 .List = Rng.CurrentRegion.Value
    
83.             End If
    
84.         ElseIf .ListIndex = -1 Then
    
85.              MsgBox "½Ð¿ï¾Ü ·s¼W¤@¦C «öÁä", , "­×§ïÀx¦s"
    
86.         End If
    
87.     End With
    
88. End Sub

½Æ»s¥N½X

ayubbs

¦^´_ 9# GBKEE


¯uªº«ÜÁÂÁÂGB¤j¤j¨C¦¸À°¦£¤p©f¸Ñ¨M°ÝÃD¡A¹ê¦b¬O«Ü¦½ÃC¡A¾Ç²ßVBA¤@¬q®É¶¡¤F¡A«Ü¦hÀ³¥Î³£°µªº¤£¦n¡A
¯u»Ý­n¥Î¤W®É¡A±`±`¥u¯à¨D§U¡A¶R¤F¤@°ï®Ñ·Q¤F¸ÑÀ³¥Î¡A¦ý®Ñ¤¤ªº¸Ñ»¡¹ê¦b¦h¼Æ©ó¹ê»ÚÀ³¥Î®É¦³«Ü¤jªº®t²§
ÁÂÁª©¤j¤£¹½¨ä·ÐÀ°¦£¸Ñ¨M°ÝÃD¡C
¨ä¤¤¦³¤@¬q ¤£¤Ó¯à¤F¸Ñ·N«ä¡A¦p¤U¬õ¦r³¡¥÷

16.Private Sub ComboBox1_Change()

17.    Dim AR, i As Integer

18.    AR = Array(TextBox1, TextBox2, TextBox3)

19.    With ComboBox1

20.        If .ListIndex > -1 Then

21.            For i = 0 To UBound(AR)

22.                AR(i).Value = .List(.ListIndex, i + 1)

23.            Next

24.            '¤@°ïªºªº±±¨î¶µ¼g¤£§¹

25.           ' TextBox1 = .List(.ListIndex, 1)     '³o´X¦æ¤£½×¬Oµù¸Ñ±¼¤Î¥¼µù¸Ñ³£¤£¼vÅT¥¿±`§@·~¡A¥Lªº§@¥Î¬°?

26.           ' TextBox2 = .List(.ListIndex, 2)
27.           ' TextBox3 = .List(.ListIndex, 3)
28.        End If

29.    End With

30.End Sub


¥t¹ê¦b«Ü¤£¦n·N«ä¡A¦³¤@¤u§@¤Wªº«æ»Ý¡A¥i§_½Ðª©¤j«üÂI¦p¦ó­×§ï

¤U¸ü (21.43 KB)
Q4

2014-12-30 11:06

¦b¤ÀÃþ¸ê®Æ¤¤­n­«Âпé¤Jªº¦r®ÉµLªk±N¸ê®Æ¿é¤J¦Ü¸ê®Æ®w¡A«ö¤U·s¼W«á¦Û°Ê²M°£
¸ê®Æ®w¤£·|·s¼W¸ê®Æ¡A¤ÎLISTBOX.2¤]¤£·|Åã¥Ü¡C
¦b³]©w¤¤¨Ã¥¼¦³¬Ý¹LÂo­«Âжµ¥Øªº¦a¤è¡A¤£ª¾¦p¦ó­×§ï¯à¹ê²{¥\¯à
¤ñ¦p ­ì¨Ó¤w¦³C+©ó²M³æ2¤F¡A­n¦A¥[¤@­ÓC+¤W¥h¦P®ÉÅã¥Ü©ó²M³æ2¤W¡A¦ý¨ä¤º®e¦³¤£¦P¥u¦³²M³æ¦WºÙ¬Û¦P
ÁÂÁª©¤j~

1. Private Sub CommandButton10_Click()
   
2.     With UserForm1
   
3.         If ListBox1.Text = "" Then
   
4.             MsgBox "±zÁÙ¥¼¿ï©w«~¶µ³á¡ã", vbCritical + vbOKOnly, "½Ð¿é¤J"
   
5.             .TextBox12.SetFocus
   
6.             Exit Sub
   
7.         End If
   
8.         mycase = Trim(.TextBox12.Text)
   
9.         If mycase = "" Then
   
10.             MsgBox "½Ð©ó¿é¤J¤ÀÃþ²M³æÄ椺¿é¤J­n·s¼W¤ÀÃþ¦WºÙ¡ã", vbCritical + vbOKOnly, "½Ð¿é¤J"
    
11.             .TextBox12.SetFocus
    
12.             Exit Sub
    
13.         Else
    
14.            
    
15.             .ListBox2.AddItem mycase
    
16.             .TextBox12.Value = ""
    
17.             .TextBox12.SetFocus
    
18.             .Frame1.Enabled = False
    
19.             .Frame3.Enabled = False
    
20.             .CommandButton6.Enabled = False
    
21.             .CommandButton11.Enabled = False
    
22.             .CommandButton12.Enabled = True
    
23.         End If
    
24.     End With
    
25.     Call CommandButton12_Click
    
26. End Sub
    
27. 
    
28. 
    
29. 
    
30. Private Sub CommandButton12_Click()
    
31.     Dim cell As Range, rng As Range
    
32.     With UserForm1
    
33.         .Frame1.Enabled = True
    
34.         .Frame3.Enabled = True
    
35.         .CommandButton6.Enabled = True
    
36.         .CommandButton6.Enabled = True
    
37.         .CommandButton11.Enabled = True
    
38.         .CommandButton12.Enabled = True
    
39.         myname = .ListBox1.Text
    
40.         Set cell = Sheets("¸ê®Æ®w").Columns("A").Find(myname, lookat:=xlWhole)
    
41.         If cell Is Nothing Then
    
42.             er = Sheets("¸ê®Æ®w").[B65536].End(3).Row + 1
    
43.             Sheets("¸ê®Æ®w").Cells(er, 1).Value = .TextBox1.Text
    
44.             Sheets("¸ê®Æ®w").Cells(er, 2).Resize(.ListBox2.ListCount, 1) = .ListBox2.List
    
45.             .TextBox1.Value = ""
    
46.        Else
    
47.             r1 = Split(d1(myname), "-")(0)
    
48.             r2 = Split(d1(myname), "-")(1)
    
49.             Set rng = Sheets("¸ê®Æ®w").Range("B" & r1 & ":B" & r2)
    
50.             For i = 0 To .ListBox2.ListCount - 1
    
51.                 Set cell = rng.Find(.ListBox2.List(i), lookat:=xlWhole)
    
52.                 If cell Is Nothing Then
    
53.                     r2 = r2 + 1
    
54.                     Sheets("¸ê®Æ®w").Rows(r2).EntireRow.Insert
    
55.                     Sheets("¸ê®Æ®w").Cells(r2, 2).Value = .ListBox2.List(i)
    
56.                 End If
    
57.             Next i
    
58.         End If
    
59.         Call UpdateBox
    
60.         .CommandButton12.Enabled = False
    
61.     End With
    
62. End Sub

½Æ»s¥N½X

TEST4.rar (42.96 KB)

¤U¸ü¦¸¼Æ: 5

2014-12-30 11:11