Macro for checkbox generation

[rmcveigh]

Hi all,

I'm not the most experienced of Excel users so please bear with me. I'
looking to generate a macro (because I'm lazy) that will place about 50
checkboxes in a single column, row by row. It also needs to move an
resize according to the data in an adjacent cell (i.e. if the text nex
to it is too large and wordwraps another line, the box must move wit
it). One checkbox per row is what I'm getting at.

This is the code I'm currently using:
Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
.CheckBoxes.Delete 'nice for setting up
For Each myCell In ActiveSheet.Range("O13:O503").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)

With myCBX
.LinkedCell = myCell.Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)

End With
.NumberFormat = ";;;"
End With

Next myCell
End With
End Sub

Please and thanks

 

[Tom Ogilvy]

with myCBX
.Placement = xlMoveAndSize
.LinkedCell = myCell.Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
End With

 

[rmcveigh]

Thanks Tom for the response.

It doesn't seem to be working (is this because we used checkboxes from
the form toolbox?). It won't even allow you to manually select the
'moveandsize' option for the boxes.

So we tried this, hoping to switch over to the controls checkbox (am I
making sense?):

Option Explicit
Sub RunOnce()

Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
..CheckBoxes.Delete 'nice for setting up
For Each myCell In ActiveSheet.Range("P13503").Cells
With myCell
Set myCBX = .Parent.OLEObjects.Add(ClassType:="Forms.CheckBox.1",
Link:=False, _
DisplayAsIcon:=False, Left:=.Left, Top:=.Top, _
Width:=.Width, Height:=.Height).Select
With myCBX
..Placement = xlMoveAndSize
..LinkedCell = myCell.Address(external:=True)
..Caption = ""
..Name = "CBX_" & myCell.Address(0, 0)
End With


..NumberFormat = ";;;"
End With

Next myCell
End With
End Sub

 

[Dave Peterson]

How about:

Option Explicit
Sub RunOnce()

Dim myCBX As OLEObject
Dim myCell As Range

With ActiveSheet
For Each myCBX In .OLEObjects
If TypeOf myCBX.Object Is MSForms.CheckBox Then
myCBX.Delete
End If
Next myCBX

For Each myCell In .Range("P1333").Cells
With myCell
Set myCBX = .Parent.OLEObjects.Add _
(ClassType:="Forms.CheckBox.1", _
Link:=False, DisplayAsIcon:=False, _
Left:=.Left, Top:=.Top, _
Width:=.Width, Height:=.Height)
With myCBX
.Placement = xlMoveAndSize
.LinkedCell = myCell.Address(external:=True)
.Object.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
End With

.NumberFormat = ";;;"
End With

Next myCell
End With
End Sub

Notice I changed the range for testing.

And I've seen excel behave not so nice when there are lots of controls from the
Control toolbox toolbar on a worksheet--you may want to keep an eye open, too.

 

[Bob Phillips]

Dim myCBX As Object
Dim myCell As Range

With ActiveSheet
'CheckBoxes.Delete 'nice for setting up
For Each myCell In ActiveSheet.Range("P1350")
With myCell
Set myCBX =
..Parent.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=.Left, _
Top:=.Top, _
Width:=.Width, _
Height:=.Height)
End With

With ActiveSheet.OLEObjects("CheckBox1") 'myCBX
.Placement = xlMoveAndSize
.LinkedCell = myCell.Address(external:=True)
.Object.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
End With

myCell.NumberFormat = ";;;"

Next myCell
End With


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Tom Ogilvy]

Sorry, I guess I read Textbox.

Dave showed you a way to replace them, but I would try it manually first. I
am not sure you will be happy with the resulting checkbox when you use
control toolbox toolbar checkboxes after you increase the row height. Also
try checking them after you increase the row height. I don't think I would
want that.

 

[rmcveigh]

Hi guys,

Thanks for all your help- the macro definitely places a sequence of
checkboxes but now, just to be picky, we're wondering how to a) enlarge
the checkboxes; b) make the boxes flat; and c) uncheck the boxes.

A pain, I know.

 

[Tom Ogilvy]

To Uncheck:
for each obj in Activesheet.OleObjects
if typeof obj.object is MSforms.Checkbox then
obj.Object.Value = false
end if
Next

their size is related to the row height. so enlarging and flattening should
be done with the row height.

 

[rmcveigh]

Thanks for the help. We figured out how to flatten and uncheck the
boxes- but the problem still remains that when the adjacent cell is
word-wrapped (and overflowing onto another line) the checkbox warps and
duplicates.

Any suggestions?

Please and thanks.

 

[Tom Ogilvy]

if a cell is word-wrapped, it doesn't overflow onto another line if you
autofit the cell and if you don't, the text is hidden.

warps and duplicates - I already told you making the row height higher isn't
pretty. Not sure what you mean beyond that or flatten either.