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

[µo°Ý] checkbox·s¼W¤Î§ó¦W

[µo°Ý] checkbox·s¼W¤Î§ó¦W

¤p§Ì²{¦b­n°µ¤@­Ó·s¼Wcheckbox·s¼W¤Î¦P®É§ï¦W¤§commonbottom
¨Ã¥B¦b«öªº¦P®É §R°£Â¦³ªºcheckbox ¨Ã·s¼W ·sªºcheckbox
¥H ²Ä¤T¦C¬°listname
¤£¹L¦b²Ä¤G¦¸«ö¤U¦¹bottom®É·|²£¥ÍµLªkºÉ¼Æ§R°£±¡§Î
·|¯d¤U²Ä¤@­ÓcheckboxµLªk§ó¦W  ³y¦¨«á­±¤§checkbox¦W¦r¬Ò¿ù
¨D¸Ñ!! ·P¿E¤£ºÉ


CK§R°£¬°§R°£²{¦³ckbox
¨ç¦¡¦p¤U
Sub CK§R°£()
   Dim CK As Object
   For Each CK In ActiveSheet.OLEObjects
    If CK.Name Like "CheckBox*" Then
        CK.Delete
    End If
   Next
   End Sub

¥H¤U¬°µ{¦¡½X

Private Sub CommandButton1_Click() '¸ê®Æ¿z¿ï
    Call CK§R°£
   
    Range("A4:K4").Select
    Range("A4:K4", Selection.End(xlDown)).Select
   
    If Selection.Count > 999999 Then '¤£±µ¨ü§C©ó¤Gµ§¸ê®Æ³B²z
        MsgBox ("¸ê®Æªí¿é¤J¸ê®Æ¤Ó¤Ö¡A½Ð½T»{¡C")
        Exit Sub
    End If
   
    '2013/07/31 Test
    Range("A3").Select
    Range("A3", Selection.End(xlToRight)).Select
    ListCount = Selection.Columns.Count
    ComboBox1.Clear
    ReDim listname(ListCount - 1) As String
    For i = 0 To ListCount - 1
        If i <= 25 Then
            listname(i) = Range(Chr(65 + i) & "3").Text
        Else
            listname(i) = Range("A" & Chr(65 + i - 25) & "3").Text
        End If
        ComboBox1.AddItem listname(i)
    Next i
   
   
     '·s¼Wcheckbox
     
     For i = 0 To ListCount - 1
        OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=0 + 93.5 * i, Top:=126, Width:=90, Height:=22.5).Select
     Next i
   
   
    'checkbox§ï¦W
     ReDim listname1(ListCount) As String
     For i = 1 To ListCount
        If i <= 25 Then
            listname1(i) = Range(Chr(64 + i) & "3").Text
        Else
            listname1(i) = Range("A" & Chr(64 + i - 25) & "3").Text
        End If
    Next i
   
     For i = 1 To ListCount
      Sheets("¸ê®Æ").OLEObjects("CheckBox" & i).Object.Caption = listname1(i)
     Next i

¹ï¤F ¸É¥R¤@¤U ¦pªG§Ú§âcall ck§R°£
¿W¥ß¤@­Óbottom¥X¨Ó³B²z  ´N¤£·|¦³³o­Ó±¡ªp
¦X¦b¤@°_¤~¥X²{ªº

TOP

¦^´_ 2# rick4615
  1. Sub Test()
  2.   Dim ar, obj, i As Long
  3.   
  4.   With Sheets("¸ê®Æ")
  5.     'Combobox
  6.     ar = Application.Transpose(Application.Transpose(.Range(.[A3], .[A3].End(xlToRight)).Value))
  7.     .ComboBox1.List = ar
  8.    
  9.     '§R°£checkbox
  10.     For Each obj In .OLEObjects
  11.       If obj.Name Like "CheckBox*" Then obj.Delete
  12.     Next
  13.    
  14.     '·s¼Wcheckbox
  15.     For i = 1 To UBound(ar)
  16.       With .OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
  17.         DisplayAsIcon:=False, Left:=0 + 93.5 * (i - 1), Top:=126, Width:=90, Height:=22.5)
  18.         .Object.Caption = ar(i)   '§ï¦W
  19.       End With
  20.     Next
  21.   End With
  22. End Sub
½Æ»s¥N½X

TOP

·PÁ¤j¤jªº¸Ñµª!
¦ü¥G§â§ï¦W¸ò·s¼W³o¨â­Ó°Ê§@¤@°_°µªº®É­Ô´N¤£·|¥X²{³oºØ°ÝÃD??
¦pªG¤À¶}¼g¦³¸Ñ¶Ü@@?

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2013-8-9 20:05 ½s¿è

¦^´_ 4# rick4615

¡@.OLEObjects("CheckBox" & i) ¡@
    ¤£¬O¤@­Ó¦w¥þªº¤èªk¨ú±oActiveXª«¥ó¡A¦³®É·|µo¥Í Run-time error 1004, Unable to get Object Property of OLEObject class

¡@§ï¥Î¡@.OLEObjects(n)   ¨ú±o¡]n¬°¼Æ¦r¡^¤ñ¸û¦w¥þ¡A¦A¥t¥~§PÂ_¦W¦r¬O§_§tCheckBox¡C

TOP

        ÀR«ä¦Û¦b : ­ì½Ì§O¤H´N¬Oµ½«Ý¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD