Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Macro to Add a CheckBox (XL2K)

    I need a macro to add a checkbox on a worksheet. So far I have
    <pre>Option Explicit

    Sub test()
    ' This macro places a checkbox in the selected cell
    Dim cb As OLEObject
    Dim c As MSForms.CheckBox
    With ActiveCell
    .RowHeight = 21
    Set cb = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Check Box.1", _
    Left:=.Left + 1, Top:=.Top + 1, Width:=1000, Height:=19.5)
    cb.Name = "chkTest"
    cb.Object.Caption = "Pretty Cool"
    cb.Object.AutoSize = True
    cb.LinkedCell = .Address
    cb.Object.Value = False
    .Offset(2, 2).Select
    End With
    End Sub</pre>


    This works, but the Name, chkTest, is associated with the OLEObject, ie it appears in the Name Box when the control is selected in the design mode. What I wanted was the actual control name to be chkTest. That way I could pre-load or dynamically create a click event. I don't think that this will be necessary, but it has me puzzled. The same code works fine for a ClassType:="Forms.CommandButton.1". Then the Name is assigned to the control. Weird! TIA --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Add a CheckBox (XL2K)

    It is much simpler to add checkboxes from the forms toolbar:

    <pre>Sub AddChecks()
    Dim dRowheight As Double
    Dim lCount As Long
    Dim oCheckbox As Object
    dRowheight = ActiveSheet.Rows(1).Height
    For lCount = 1 To 10
    Set oCheckbox = ActiveSheet.CheckBoxes.Add(Top:=(lCount - 1) * dRowheight _
    , Left:=10, Height:=dRowheight, Width:=60)
    With oCheckbox
    .Caption = "Box " & lCount
    .Name = "Checkbox_" & lCount
    .OnAction = "CheckBoxes_Onclick"
    End With
    Next
    End Sub
    Sub CheckBoxes_Onclick()
    Dim sCaller As String
    sCaller = Application.Caller
    If ActiveSheet.CheckBoxes(sCaller).Value <> -4146 Then
    MsgBox sCaller & " is checked."
    Else
    MsgBox sCaller & " is not checked."
    End If
    End Sub
    </pre>


    If you still wish to use checkboxes from the control toolbox, I would create a class module that handles the click event of each checkbox. Then all you need to do is instantiate the event handler for the checkboxes.

    The coding involved is rather tricky and too complicatied to describe here. It is well described in some VBA books though.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro to Add a CheckBox (XL2K)

    Sammy ,

    I'm not sure what problem you are having, but your code works fine for me. It creates a CheckBox named chkTest. Altering it to create a CommandButton also works in the same way. The following piece of code should add a Click event to the chkText checkbox :

    Dim shtModule
    Dim strOnlick As String
    Set shtModule = ThisWorkbook.VBProject.VBComponents(ActiveSheet.Na me).CodeModule
    strOnClick = "Private Sub chkTest_Click" & _
    vbCrLf & vbTab & "MsgBox " & Chr(34) _
    & "Testing chkTest " & Chr(34) & vbCrLf & "End Sub"
    With shtModule
    .InsertLines .CountOfLines + 1, strCode
    End With
    Set shtModule = Nothing

    Also see the attached capture of the properties for both a Commandbutton and Checkbox created with your method.

    Andrew
    Attached Images Attached Images

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro to Add a CheckBox (XL2K)

    I fought Excel until I couldn't stand it, then I rewrote it your way. It works perfectly, plus it saved me time in the long run when I needed them all to call the same macro. Thanks, Jan Karel!

    BTW, not that it matters, but being old, I wanted to increase the font size of the checkbox text. Can it be done? I couldn't find any such property. Also, can you figure out any good way to "autosize" the width of the checkbox? I load the text in at run-time and set the forecolor to light green to get the user's attention. Only thing that I could figure out to do was write the captions into an unused column on the worksheet, do an autofit, and use the resulting column width plus the width of the "check." Thanks! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro to Add a CheckBox (XL2K)

    There is no way to adjust the size of the text in the FORMS toolbar (other than playing with the zoom and the fontsize of the sheet).

    The "ease of use" of the FORMS toolbar items comes at the price that there is not a lot of customization. The CONTROL Toolbar items are more difficult to use, but can be customized.
    Steve

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Add a CheckBox (XL2K)

    I guess your workaround to autofit is as good as any. No other way to do this than by trial and error.

    Steve said it all: Forms controls are simpler to use and hence limited in their formatting options.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    2 Star Lounger
    Join Date
    Nov 2002
    Posts
    134
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro to Add a CheckBox (XL2K)

    Could you please explain how to size the box using the control toolbar? I have just tried very briefly and nothing stands out to me as being very useful. A situation just came up with a form we created where the client is telling us that the check boxes are "too small." We would like to maintain the ability to automatically check the boxes, but can't figure out how to increase the size of the boxes. Thanks.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to Add a CheckBox (XL2K)

    I don't think the size of a check box can be changed. Take a look at Pieterse's reply to a similar question in <post#=226630>post 226630</post#>.

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro to Add a CheckBox (XL2K)

    There is a slight confusion over the meaning of box here. I was referring to the entire control as a box. You can resize this easily, but it does not affect the size of the font nor the size of the checkbox-box. In my case, I fill everything in light green that requires user input, so I wanted all of the checkbox controls to have the same width. You might want to just increase the size of the control, since clicking anywhere on the control causes the box to be checked. If it is a forms checkbox, then you could right-click and have a border line and/or fill and explain to the user that you can click anwhere on the control. If you have a VBA checkbox, then you enter the design mode and change the properties. With this control, you can increase the font size, but not the little checkbox-box. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro to Add a CheckBox (XL2K)

    The actual box part of a check box cannot be resized, regardless of which type you are using.

    See this thread, for a possible alternative.

    Andrew C

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •