Aligning check boxes in Excel 2007

[DoubleZ]

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.

 

[Chip Pearson]

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)

 

[Andy Pope]

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

 

[DoubleZ]

Thanks to both of you for your help.

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)

 

[travwoody]

Code Question on this thread

Good evening....
I need an update on this if possible. Any new way to do this in Excel 2007? Also sorry but I am new to the code in Excel. Is this sheet or module code?

Thanks Much!

 

[travwoody]

Actually I figured this out.....the code works great, but only with one column of boxes. Is there a way to make something like this for multiple columns of check boxes, for instance three columns, m, n, o in the sheet?