Macro for checkbox generation
 

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

--
rmcveig
-----------------------------------------------------------------------
rmcveigh's Profile: http://www.excelforum.com/member.php...fo&userid=3735
View this thread: http://www.excelforum.com/showthread.php?threadid=57145

Macro for checkbox generation
 

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

--
Regards,
Tom Ogilvy

"rmcveigh" wrote:


Hi all,

I'm not the most experienced of Excel users so please bear with me. I'm
looking to generate a macro (because I'm lazy) that will place about 500
checkboxes in a single column, row by row. It also needs to move and
resize according to the data in an adjacent cell (i.e. if the text next
to it is too large and wordwraps another line, the box must move with
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.


--
rmcveigh
------------------------------------------------------------------------
rmcveigh's Profile: http://www.excelforum.com/member.php...o&userid=37353
View this thread: http://www.excelforum.com/showthread...hreadid=571452

Macro for checkbox generation
 

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("P13:P503").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


--
rmcveigh
------------------------------------------------------------------------
rmcveigh's Profile: http://www.excelforum.com/member.php...o&userid=37353
View this thread: http://www.excelforum.com/showthread...hreadid=571452

Macro for checkbox generation
 

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("P13:P33").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.


rmcveigh wrote:

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("P13:P503").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

--
rmcveigh
------------------------------------------------------------------------
rmcveigh's Profile: http://www.excelforum.com/member.php...o&userid=37353
View this thread: http://www.excelforum.com/showthread...hreadid=571452

--

Dave Peterson

Macro for checkbox generation
 

Dim myCBX As Object
Dim myCell As Range

With ActiveSheet
'CheckBoxes.Delete 'nice for setting up
For Each myCell In ActiveSheet.Range("P13:P50")
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)

"rmcveigh" wrote in
message ...

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("P13:P503").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


--
rmcveigh
------------------------------------------------------------------------
rmcveigh's Profile:
http://www.excelforum.com/member.php...o&userid=37353
View this thread: http://www.excelforum.com/showthread...hreadid=571452

Macro for checkbox generation
 

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.

--
Regards,
Tom Ogilvy


"rmcveigh" wrote:


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("P13:P503").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


--
rmcveigh
------------------------------------------------------------------------
rmcveigh's Profile: http://www.excelforum.com/member.php...o&userid=37353
View this thread: http://www.excelforum.com/showthread...hreadid=571452

Macro for checkbox generation
 

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. :)


--
rmcveigh
------------------------------------------------------------------------
rmcveigh's Profile: http://www.excelforum.com/member.php...o&userid=37353
View this thread: http://www.excelforum.com/showthread...hreadid=571452

Macro for checkbox generation
 

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.

--
Regards,
Tom Ogilvy






"rmcveigh" wrote:


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. :)


--
rmcveigh
------------------------------------------------------------------------
rmcveigh's Profile: http://www.excelforum.com/member.php...o&userid=37353
View this thread: http://www.excelforum.com/showthread...hreadid=571452

Macro for checkbox generation
 

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.


--
rmcveigh
------------------------------------------------------------------------
rmcveigh's Profile: http://www.excelforum.com/member.php...o&userid=37353
View this thread: http://www.excelforum.com/showthread...hreadid=571452

Macro for checkbox generation
 

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.

--
Regards,
Tom Ogilvy


"rmcveigh" wrote:


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.


--
rmcveigh
------------------------------------------------------------------------
rmcveigh's Profile: http://www.excelforum.com/member.php...o&userid=37353
View this thread: http://www.excelforum.com/showthread...hreadid=571452