Macro for checkbox generation

[rmcveigh]

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.

[Tom Ogilvy]

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
>
>

[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. We need to create a series of checkboxes that will move and size according to an adjacent cell.

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

[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("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

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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.

--
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
>
>

[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.

--
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
>
>

[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.

--
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
>
>