Help with Excel Macro: Create Check Boxes

[Jenova314]

So I'd like to create a column of check boxes. In addition, I'd like to be able to remove the default "alternative text" associated with these check boxes. Those of you who have used these boxes in Excel know they are pretty fickle. I cannot copy them, along with their linked cells, and spread them down a column, expecting the "linked cell" value to change accordingly.

I've come up with a macro:

Public Sub LotsOfCheckboxes()
Dim cell As Range
For Each cell In Range("D4200")
With cell
With ActiveSheet.CheckBoxes.Add(.Left, .Top, 20, 20)
.AlternativeText = ""
.Value = xlOff
.LinkedCell = cell.Address
.Display3DShading = False
End With
End With
Next cell
End Sub

The bolded text is the offending line that isn't doing what I'd like to do. Can anybody help me out with this? I'm certain it's a piece of cake for many, but I'm quite a noob at this, as I don't know the syntax.

 

[jersiq]

I am just learning VBA myself, so my code may not be the most efficient.

Sub checkboxes()
Dim ctl
Dim rng As Range
Dim c As Range
Set rng = ActiveSheet.Range("d4200")

For Each c In rng.Cells
Set ctl = ActiveSheet.checkboxes.Add(c.Left, c.Top, c.Width, c.Height)
With ctl
.Text = ""
.Value = xlOff
.LinkedCell = c.Offset(, -1).Address
.Display3DShading = False
End With
Next

End Sub

The linked cell reference would be one column to the left of the checkbox. So the fourth row would have D4 with a box, and that box would be linked to C4.
You may have to adjust your dimensions as appropriate for the checkboxes, it just looked odd when I ran it with your dimensions.

Also adjust your column offset for the linked cells as you need using the R1C1 format.

 

[Jenova314]

Hey jersiq,

That worked like a charm. Thanks a million! Also, wouldn't you happen to know how to offset the check boxes themselves? Say... 1 pixel to the right, 3 pixels up? I'd have done it myself if I could see the list of syntax

 

[TSDible]

Originally posted by: Jenova314
Hey jersiq,

That worked like a charm. Thanks a million! Also, wouldn't you happen to know how to offset the check boxes themselves? Say... 1 pixel to the right, 3 pixels up? I'd have done it myself if I could see the list of syntax

Try this:

Sub checkboxes()
Dim ctl
Dim rng As Range
Dim c As Range
Set rng = ActiveSheet.Range("D4200")

For Each c In rng.Cells
Set ctl = ActiveSheet.checkboxes.Add(c.Left, c.Top, c.Width, c.Height)
With ctl
.Text = ""
.Value = xlOff
.LinkedCell = c.Offset(, -1).Address
.Display3DShading = False
.ShapeRange.IncrementLeft 1
.ShapeRange.IncrementTop -3
End With
Next

End Sub