Check boxes
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. |
Check boxes
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. On 03/08/2011 21:23, Slim Slender wrote: 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 |
Check boxes
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. |
Check boxes
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. On 03/09/2011 19:36, Slim Slender wrote: 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 |
Check boxes
On Mar 10, 7:31*am, Dave Peterson wrote:
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. On 03/09/2011 19:36, Slim Slender wrote: 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 Thanks again for the help and the additional information. |
All times are GMT +1. The time now is 01:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com