adding and aligning Check Boxes with a Macro?

[AZEXCELAZ]

Hello All,

I am trying to add Check boxes to a spreadsheet (Column A). I have 2000+ lines to add it to, and am trying to figure out a way to do it efficiently. I need to add a text box, align it to a cell and tie it to the cell to the right of it (Column C).

Right now, I copy an existing text box and paste several more in there. They fall into the spreadsheet in a cascade effect and are all tied to the cell that the original text box is tied to. I then manually align, right-click -- format control -- cell link, and enter the appropriate cell.

Can I do this with a Macro? Or is there some other way?


-- removed inline image ---

 

[Gary McMaster]

Below is some code that will create check boxes dynamically. You started off mentioning check boxes and then changed to text boxes in the middle of your post. I'm not sure which one you are looking for.

Gary

Please test in a standard module in a new workbook.
Run "CheckBox_Demo_Create" to begin with.

Option Explicit

Sub CheckBox_Demo_Create()

Dim oShape As Shape
Dim oActive As Worksheet
Dim oCheckBox As CheckBox
Dim oRange As Range
Dim oCell As Range

Dim lWidth As Long
Dim lHeight As Long

lWidth = 100
lHeight = 12

Set oActive = ActiveSheet

Set oRange = oActive.Range("B1:B10")
oRange.ColumnWidth = 16
oRange.Offset(0, 1).ColumnWidth = 60

For Each oShape In oActive.Shapes
    If InStr(1, oShape.Name, "CheckBox") Then
        oShape.Delete
    End If
Next oShape

For Each oCell In oRange
    Set oCheckBox = oActive.CheckBoxes.Add(oCell.Left, oCell.Top, lWidth, lHeight)
    With oCheckBox
        '.LinkedCell = oCell.Address
        '.Interior.ColorIndex = 3
        .Name = "CheckBox_" & oCell.Address
        .Caption = .Name
        .OnAction = "Checkbox_Toggle"
    End With
Next oCell

End Sub

Public Sub CheckBox_Demo_Check_Uncheck()

Dim oShape As Shape
Dim oActive As Worksheet
Dim oCell As Range

Set oActive = ActiveSheet

For Each oShape In oActive.Shapes

If oShape.Type = msoFormControl Then
    If oShape.FormControlType = xlCheckBox Then
        If InStr(1, oShape.Name, "CheckBox") Then
            Set oCell = oShape.TopLeftCell
            If oShape.ControlFormat.Value = Checked Then
                oShape.ControlFormat.Value = Unchecked
            Else
                oShape.ControlFormat.Value = Checked
            End If
        End If
    End If
End If

Next oShape

End Sub

Public Sub Checkbox_Toggle()

Dim oShape As Shape
Dim oCell As Range
Dim oActive As Worksheet

Set oActive = ActiveSheet

Set oShape = oActive.Shapes(Application.Caller) 'Referenced as shape (see below)

Set oCell = oShape.TopLeftCell

If oShape.ControlFormat.Value = Checked Then
    oCell.Offset(0, 1).Value = "The check box in cell" & oCell.Address & " named " & oShape.Name & " was checked"
Else
    oCell.Offset(0, 1).Value = "The check box in cell" & oCell.Address & " named " & oShape.Name & " was un-checked"
End If

End Sub

 

[AZEXCELAZ]

Sorry, I need help with Check Boxes (not text boxes). I will try that code. Thanks

 

[AZEXCELAZ]

That code works great. I was able to modify the output to True or False depending on whether the check box was checked or not, and I was able to increase the boxes created from 10 to 100.
I need direction on two things:
1. I need the check box to stand alone with no words next to it. Currently it reads "Checkbox_$D$2" and "Checkbox_$D$3" and so on. How can I hide that label?
2. I need to increase it to create 2000 check boxes. When I do this, Excel freezes. I leave it alone, but it never seems to recover. Thanks

 

[Gary McMaster]

Change:

.Caption = .Name

to:

.Caption = ""

2000 is an awful lot of check boxes. Apparently it's taxing the system. All I can suggest is devise another method that doesn't use so many.

Gary

 

[Gary McMaster]

Maybe try some variation of this:

http://datapigtechnologies.com/blog/index.php/adding-check-boxes-without-form-controls/#more-3017

Gary

 

[AZEXCELAZ]

This works perfectly! Thanks Gary!!