ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

¥»©«³Ì«á¥Ñ ayubbs ©ó 2014-12-16 01:22 ½s¿è

¤£¦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)



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

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2014-12-16 11:14 ½s¿è

¦^´_ 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. Private Sub CommandButton2_Click()
  13.   TextBox1.Value = ""
  14. 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. Private Sub UpdateTextBox4()
  14.   Dim s As String, i As Long
  15.   For i = 5 To 8
  16.     With Controls("CheckBox" & i)
  17.       If .Value Then
  18.         s = IIf(s = "", .Caption, s & ";" & .Caption)
  19.       End If
  20.     End With
  21.   Next
  22.   TextBox4.Value = s
  23. End Sub
½Æ»s¥N½X
P.S.  
ÂI¿ïcheckbox¦³­ÓÄÝ©Ê¥sGroupName
ABC À³¸Ó³]¸m¦P¤@­ÓGroupName
¤j¤¤¤p ³]¸m¥t¤@­ÓGroupName
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_ 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¥[

ÁÂÁÂ
½Ð¦h¦h«ü±Ð

TOP

¦^´_ 3# ayubbs
¤@°ïªºCheckBox¥i¥Îª«¥óÃþ§O¼Ò²Õ,¼g¤U¦@¥Îªºµ{§Ç
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 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

LABLE.gif

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



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

lable.rar (15.28 KB)
½Ð¦h¦h«ü±Ð

TOP

¦^´_ 4# GBKEE


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



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

TOP

¦^´_ 5# ayubbs
¨£ªþÀÉ¡AÁÙ¦³¬OLabel¤£¬OLable
lable.zip (15.79 KB) la
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_ 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¤£¶Þ«ü¾É
½Ð¦h¦h«ü±Ð

TOP

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 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¦ó­×§ï



¦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. Private Sub CommandButton12_Click()
  28.     Dim cell As Range, rng As Range
  29.     With UserForm1
  30.         .Frame1.Enabled = True
  31.         .Frame3.Enabled = True
  32.         .CommandButton6.Enabled = True
  33.         .CommandButton6.Enabled = True
  34.         .CommandButton11.Enabled = True
  35.         .CommandButton12.Enabled = True
  36.         myname = .ListBox1.Text
  37.         Set cell = Sheets("¸ê®Æ®w").Columns("A").Find(myname, lookat:=xlWhole)
  38.         If cell Is Nothing Then
  39.             er = Sheets("¸ê®Æ®w").[B65536].End(3).Row + 1
  40.             Sheets("¸ê®Æ®w").Cells(er, 1).Value = .TextBox1.Text
  41.             Sheets("¸ê®Æ®w").Cells(er, 2).Resize(.ListBox2.ListCount, 1) = .ListBox2.List
  42.             .TextBox1.Value = ""
  43.        Else
  44.             r1 = Split(d1(myname), "-")(0)
  45.             r2 = Split(d1(myname), "-")(1)
  46.             Set rng = Sheets("¸ê®Æ®w").Range("B" & r1 & ":B" & r2)
  47.             For i = 0 To .ListBox2.ListCount - 1
  48.                 Set cell = rng.Find(.ListBox2.List(i), lookat:=xlWhole)
  49.                 If cell Is Nothing Then
  50.                     r2 = r2 + 1
  51.                     Sheets("¸ê®Æ®w").Rows(r2).EntireRow.Insert
  52.                     Sheets("¸ê®Æ®w").Cells(r2, 2).Value = .ListBox2.List(i)
  53.                 End If
  54.             Next i
  55.         End If
  56.         Call UpdateBox
  57.         .CommandButton12.Enabled = False
  58.     End With
  59. End Sub
½Æ»s¥N½X
TEST4.rar (42.96 KB)
½Ð¦h¦h«ü±Ð

TOP

        ÀR«ä¦Û¦b : ¥@¤W¦³¨â¥ó¨Æ¤£¯àµ¥¡G¤@¡B§µ¶¶ ¤G¡B¦æµ½¡C
ªð¦^¦Cªí ¤W¤@¥DÃD