Add Checkboxes through VBA code

[tazz]

Hi,

I'm trying to add a series of checkboxes (Excel not activeX) to a worksheet. I'd like to have code that would create the check box and associate it with a cell based on a condition. So, if a cell in Column D has a value (or is not blank) I'd like to have a check box created in Column C and the checkbox be associated to the cell (D2 has a value, check box is created in C2 and C2 now displays True/False base on check box value). I can do this the old fashioned way, but I add 30 - 50 users at a time and that's a long tedious process.

Any thoughts?

Regards,

Tazz

 

[BrianB]

This is the sort of thing. Need to tweak a bit because checkbox control overall size is bigger than the actual box. You might also be interested in my alternative.

Sub test()
    Dim ToRow As Long
    Dim LastRow As Long
    Dim MyLeft As Double
    Dim MyTop As Double
    Dim MyHeight As Double
    Dim MyWidth As Double
    '--------------------------
    LastRow = Range("D65536").End(xlUp).Row
    For ToRow = 2 To LastRow
        If Not IsEmpty(Cells(ToRow, "D")) Then
            '-
            MyLeft = Cells(ToRow, "C").Left
            MyTop = Cells(ToRow, "C").Top
            MyHeight = Cells(ToRow, "C").Height
            MyWidth = MyHeight = Cells(ToRow, "C").Width
            '-
            ActiveSheet.CheckBoxes.Add(MyLeft, MyTop, MyWidth, MyHeight).Select
             With Selection
                .Caption = ""
                .Value = xlOff
                .LinkedCell = "C" & ToRow
                .Display3DShading = False
            End With
        End If
    Next
End Sub

http://www.mrexcel.com/board2/viewtopic.php?t=154562&highlight=

 

[Lamar in Houston]

Properties Window is not available...`

I tried this as well and it works beautifully. My only question is how do I modify the properties of the checkboxes created? I try clicking on one to show the properties but it only check the box.

Thnaks

Lamar

 

[Lamar in Houston]

Properties Window is not available...`

I tried this as well and it works beautifully. My only question is how do I modify the properties of the checkboxes created? I try clicking on one to show the properties but it only check the box.

Thnaks

Lamar

 

[tazz]

Lamar,

I think if you click on the design button you can then see the checkbox properties.

HTH,
Tazz

 

[BrianB]

Right click the control and Format Control.
If you record a macro of your setting you can incorporate it into the overall thing like this :-

Sub test()
    Dim ToRow As Long
    Dim LastRow As Long
    Dim MyLeft As Double
    Dim MyTop As Double
    Dim MyHeight As Double
    Dim MyWidth As Double
    '--------------------------
    LastRow = Range("D65536").End(xlUp).Row
    For ToRow = 2 To LastRow
        If Not IsEmpty(Cells(ToRow, "D")) Then
            '-
            MyLeft = Cells(ToRow, "C").Left
            MyTop = Cells(ToRow, "C").Top
            MyHeight = Cells(ToRow, "C").Height
            MyWidth = MyHeight = Cells(ToRow, "C").Width
            '-
            ActiveSheet.CheckBoxes.Add(MyLeft, MyTop, MyWidth, MyHeight).Select
             With Selection
                .Caption = ""
                .Value = xlOff
                .LinkedCell = "C" & ToRow
                .Display3DShading = False
                '------------------------------------------------------
                '- format
                .Placement = xlFreeFloating
                .PrintObject = True
                '-------------------------------------------------------
                .ShapeRange.Fill.Visible = msoTrue
                .ShapeRange.Fill.Solid
                .ShapeRange.Fill.ForeColor.SchemeColor = 13
                .ShapeRange.Fill.Transparency = 0#
                .ShapeRange.Line.Weight = 0#
                .ShapeRange.Line.DashStyle = msoLineSolid
                .ShapeRange.Line.Style = msoLineSingle
                .ShapeRange.Line.Transparency = 0#
                .ShapeRange.Line.Visible = msoTrue
                .ShapeRange.Line.ForeColor.SchemeColor = 64
                .ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
                '-------------------------------------------------------------
            End With
        End If
    Next
End Sub

 

[ChompGator]

Im working on a similar problem...Except if you look at my sheet, I have included a link to download it here:
http://www.aiimcalgary.org/newtest.xls
I have command buttons that can create word documents.
(Its in Column Y)

However within the body of the Word Document is a list of file numbers which comes from column Q.

I would like to preform a similar task, have a checkbox in every cell in column Q that has data...That way users can select which file numbers they want, then can scroll over to the command button "Create Transmittal" and once they select the command button those file numbers checked, will be listed in the body of the word document.

Is that possible to do?

 

[Cirrostratus]

This worked great, now to add to this. How do I add a date stamp to column I when the checkbox is clicked? Appreciate the help!

-tom

 

[san1986]

Hi guys,

I am new to this, however, I am also interested in above topic. I copy paste the above code from BrianB in VB and then run it, but I didn't get any output in my excel. Am I doing in the right way? I appreciate your advise. Thank you!