Aligning check boxes in Excel 2007
 

In Excel 2007 I have check boxes in many cells in one column. I haven't
worked with check boxes much and I can't figure out how to line them up
evenly. I looked over the previous posts on the subject and found out how to
do it in excel 2002, but the ribbon system of 2007 doesn't allow for the same
solution.

Thanks for any advice you may have.

Aligning check boxes in Excel 2007
 

The method depends on whether you are using the ActiveX check boxes or
the Forms checkboxes. The code below illustrates both.


Sub AAA()
' ActiveX checkboxes
Dim OleObj As OLEObject
Dim L As Double
L = -1
For Each OleObj In ActiveSheet.OLEObjects
If TypeOf OleObj.Object Is msforms.CheckBox Then
If L < 0 Then
L = OleObj.Left
End If
OleObj.Left = L
End If
Next OleObj

' Forms checkboxes
Dim ChB As Excel.CheckBox
With ActiveSheet.CheckBoxes
If .Count 0 Then
For Each ChB In ActiveSheet.CheckBoxes
ChB.Left = .Item(1).Left
Next ChB
End If
End With
End Sub


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Thu, 16 Oct 2008 13:55:01 -0700, DoubleZ
wrote:

In Excel 2007 I have check boxes in many cells in one column. I haven't
worked with check boxes much and I can't figure out how to line them up
evenly. I looked over the previous posts on the subject and found out how to
do it in excel 2002, but the ribbon system of 2007 doesn't allow for the same
solution.

Thanks for any advice you may have.

Aligning check boxes in Excel 2007
 

Hi,

If you are doing this manually the major problem appears to be the selecting
of multiple objects.
If you want ALL objects on the sheet selected you can use CTRL+G Special
Objects.
There is a Selection tool, Home Editing Find & Select Select Objects.
But this appears to ignore the controls.

Once you have the controls selected you can use the Alignment options.
Page Layout Arrange Align Align Left.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"DoubleZ" wrote in message
...
In Excel 2007 I have check boxes in many cells in one column. I haven't
worked with check boxes much and I can't figure out how to line them up
evenly. I looked over the previous posts on the subject and found out how
to
do it in excel 2002, but the ribbon system of 2007 doesn't allow for the
same
solution.

Thanks for any advice you may have.

Aligning check boxes in Excel 2007
 

Thanks to both of you for your help.

"Chip Pearson" wrote:

The method depends on whether you are using the ActiveX check boxes or
the Forms checkboxes. The code below illustrates both.


Sub AAA()
' ActiveX checkboxes
Dim OleObj As OLEObject
Dim L As Double
L = -1
For Each OleObj In ActiveSheet.OLEObjects
If TypeOf OleObj.Object Is msforms.CheckBox Then
If L < 0 Then
L = OleObj.Left
End If
OleObj.Left = L
End If
Next OleObj

' Forms checkboxes
Dim ChB As Excel.CheckBox
With ActiveSheet.CheckBoxes
If .Count 0 Then
For Each ChB In ActiveSheet.CheckBoxes
ChB.Left = .Item(1).Left
Next ChB
End If
End With
End Sub


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Thu, 16 Oct 2008 13:55:01 -0700, DoubleZ
wrote:

In Excel 2007 I have check boxes in many cells in one column. I haven't
worked with check boxes much and I can't figure out how to line them up
evenly. I looked over the previous posts on the subject and found out how to
do it in excel 2002, but the ribbon system of 2007 doesn't allow for the same
solution.

Thanks for any advice you may have.