Check boxes

[Slim Slender]

I'm using the following bit of code adapted from something I found
here to place some check boxes on a worksheet.

Sub InsertCheckBoxes()
'from Dave Peterson

Dim i As Long

Const firstrow As Long = 2
Const lastrow As Long = 18
Const cb_col As Long = 3 'column C

For i = firstrow To lastrow
With Cells(i, cb_col)
If Cells(i, 2).Value Like "*Pend*" Then
With
ActiveSheet.CheckBoxes.Add(.Left, .Top, .Width, .Height)
' .Name = "cb" & Format(i - 1, "000")
.Caption = ""
End With
End If
End With
Next i

End Sub

I've taken out a few bits I didn't need. What I find by right clicking
on a check box is that the "container" for the check box fills the
cell it is in and the check box is to the left. I was wondering if it
would be possible to programmatically reduce the size of the container
around the check box and make it flush right in the cell, oh, and one
other thing, can the container background be made white or opaque.

 

[Dave Peterson]

I like to shrink the column to the exact width of the checkbox -- and I make the
checkbox caption "", so that it's just a checkbox in the cell.

But you could fiddle around by offsetting the location of the checkbox with
something like:

Option Explicit
Sub InsertCheckBoxes()
'from Dave Peterson

Dim iCtr As Long

Const FirstRow As Long = 2
Const LastRow As Long = 18
Const CBXCol As Long = 3 'column C
Dim myOffset As Double

myOffset = 35


With ActiveSheet
.CheckBoxes.Delete 'nice for testing
For iCtr = FirstRow To LastRow
If .Cells(iCtr, 2).Value Like "*Pend*" Then
With .Cells(iCtr, CBXCol)
With .Parent.CheckBoxes.Add _
(Top:=.Top, _
Width:=.Width - myOffset, _
Left:=.Left + myOffset, _
Height:=.Height)
.Name = "cbx_" & Format(iCtr - 1, "000")
.Caption = ""
End With
End With
End If
Next iCtr
End With

End Sub

It worked ok with my test worksheet. But it'll depend on the width of column C.

For me, the checkbox had a white fill color, so I'm not sure what you mean.

But you may be able to determine your code by recording a macro when you change
the property that you want.

 

[Slim Slender]

Thanks Dave,
That did it. The thing about changing the backcolor of the control was
in case nothing else worked and I had to have it flush left in the
cell on top of text but this solves that by moving it to the right. So
Parent refers to the container for the checkbox, the dotted box with
the handles? I suppose that is the same for other controls. What units
is the offset of 35 in? It doesn't seem to be the same unit as the
number you get when you check the column width.

 

[Dave Peterson]

The objects/properties that start with dots like the .checkboxes, .cells and
..parent in this section of code:

With ActiveSheet
.CheckBoxes.Delete 'nice for testing
For iCtr = FirstRow To LastRow
If .Cells(iCtr, 2).Value Like "*Pend*" Then
With .Cells(iCtr, CBXCol)
With .Parent.CheckBoxes.Add _

mean that each belongs to the object in the previous With statement.

So .checkboxes.delete belongs to the activesheet.
..cells(ictr,2).value belongs to the activesheet.
..cells(ictr,cbxcol) belongs to the activesheet.
..parent.checkboxes.add belongs to the .cells(ictr,cbxcol) which belongs to the
activesheet.

And the .parent of a range is a worksheet. The .parent of a sheet is the
workbook and the .parent of a workbook is the application (excel).

======
The .width of the cell is measured in points. But I really don't bother with
the details too much -- I don't care if it's measured in points, pixels, inches,
or even miles!

I usually just experiment to see what looks right. That's why I added that
..checkboxes.delete line to the code. It took me a few times to find the correct
offset in my test worksheet.

Remember that you can always add more info to your code to see:
msgbox .cells(ictr,cbxcol).width
may give you a hint.

 

[Slim Slender]

The objects/properties that start with dots like the .checkboxes, .cells and
.parent in this section of code:

     With ActiveSheet
         .CheckBoxes.Delete 'nice for testing
         For iCtr = FirstRow To LastRow
             If .Cells(iCtr, 2).Value Like "*Pend*" Then
                 With .Cells(iCtr, CBXCol)
                     With .Parent.CheckBoxes.Add _

mean that each belongs to the object in the previous With statement.

So .checkboxes.delete belongs to the activesheet.
.cells(ictr,2).value belongs to the activesheet.
.cells(ictr,cbxcol) belongs to the activesheet.
.parent.checkboxes.add belongs to the .cells(ictr,cbxcol) which belongs to the
activesheet.

And the .parent of a range is a worksheet.  The .parent of a sheet is the
workbook and the .parent of a workbook is the application (excel).

======
The .width of the cell is measured in points.  But I really don't bother with
the details too much -- I don't care if it's measured in points, pixels, inches,
or even miles!

I usually just experiment to see what looks right.  That's why I added that
.checkboxes.delete line to the code.  It took me a few times to find the correct
offset in my test worksheet.

Remember that you can always add more info to your code to see:
msgbox .cells(ictr,cbxcol).width
may give you a hint.

Thanks again for the help and the additional information.