checkbox hell
 

the reason is the procedure only applys to active sheet
so u may make a loop to walk through

Private Sub CommandButton1_Click()
Dim myObj As OLEObject
For Each myObj In ActiveWorkbook.ActiveSheet.OLEObjects
Debug.Print myObj.Name
If Len(myObj.Name) 8 Then
If Left(myObj.Name, 8) = "CheckBox" Then
myObj.Left = 50
End if
End If
Next
End Sub

checkbox hell
 

Oh... I'm sorry but I don't think I explained my problem
clearly. I have two sheets with checkboxes. On sheet1, I
have four that I would like to set the checkbox left
property to 50. This is done by the following procedure,
which is invoked by clicking the command button on sheet1:

Private Sub CommandButton1_Click()
Dim myObj As OLEObject
For Each myObj In ActiveWorkbook.ActiveSheet.OLEObjects
Debug.Print myObj.Name
If Len(myObj.Name) = 8 Then
If Left(myObj.Name, 8) = "CheckBox" Then
myObj.Left = 50
End If
End If
Next
End Sub

On sheet2, I have a sample of what appears to be checkbox
controls and I attempted to do the same thing as I did for
the checkboxes on sheet1 with the following code (also
triggered with a command button on sheet2):

Private Sub CommandButton1_Click()
Dim myObj As OLEObject
For Each myObj In ActiveWorkbook.ActiveSheet.OLEObjects
Debug.Print myObj.Name
If Len(myObj.Name) = 9 Then
If Left(myObj.Name, 9) = "Check Box" Then
myObj.Left = 50
End If
End If
Next
End Sub

Here is the problem... the For Each...Next loop only finds
one OLE Objects... the command button. I don't understand
why the checkboxes' names appear in the debug window --
that is, unless they are not an OLEObject. But if that is
true, then what are they? Why can't I access the checkbox
properties in design mode?

If anyone has any idea... I'd really appreciate the help!
I've attached a copy of the excel file to my first message
of this thread.

Thanks,

Robert

-----Original Message-----
the reason is the procedure only applys to active sheet
so u may make a loop to walk through

checkbox hell
 

Hi Robert,

If your checkboxes are from the Forms toolbar, not Control
toolbox, try this:

sub test()
Dim cb As CheckBox
For Each cb In ActiveSheet.CheckBoxes
cb.Left = 100
Next
end sub

Have you thought of aligning with cells.
FWIW I've had problems dimensioning objects if
Zoom is not %100.

Regards,
Sandy


-----Original Message-----
Oh... I'm sorry but I don't think I explained my problem
clearly. I have two sheets with checkboxes. On sheet1,
I
have four that I would like to set the checkbox left
property to 50. This is done by the following procedure,
which is invoked by clicking the command button on sheet1:

Private Sub CommandButton1_Click()
Dim myObj As OLEObject
For Each myObj In ActiveWorkbook.ActiveSheet.OLEObjects
Debug.Print myObj.Name
If Len(myObj.Name) = 8 Then
If Left(myObj.Name, 8) = "CheckBox" Then
myObj.Left = 50
End If
End If
Next
End Sub

On sheet2, I have a sample of what appears to be checkbox
controls and I attempted to do the same thing as I did
for
the checkboxes on sheet1 with the following code (also
triggered with a command button on sheet2):

Private Sub CommandButton1_Click()
Dim myObj As OLEObject
For Each myObj In ActiveWorkbook.ActiveSheet.OLEObjects
Debug.Print myObj.Name
If Len(myObj.Name) = 9 Then
If Left(myObj.Name, 9) = "Check Box" Then
myObj.Left = 50
End If
End If
Next
End Sub

Here is the problem... the For Each...Next loop only
finds
one OLE Objects... the command button. I don't
understand
why the checkboxes' names appear in the debug window --
that is, unless they are not an OLEObject. But if that
is
true, then what are they? Why can't I access the
checkbox
properties in design mode?

If anyone has any idea... I'd really appreciate the
help!
I've attached a copy of the excel file to my first
message
of this thread.

Thanks,

Robert

-----Original Message-----
the reason is the procedure only applys to active sheet
so u may make a loop to walk through


.

checkbox hell
 

THANK YOU Sandy! I've never noticed there was a Forms
Toolbar. Thanks for the help!

Robert


-----Original Message-----
Hi Robert,

If your checkboxes are from the Forms toolbar, not
Control
toolbox, try this:

sub test()
Dim cb As CheckBox
For Each cb In ActiveSheet.CheckBoxes
cb.Left = 100
Next
end sub

Have you thought of aligning with cells.
FWIW I've had problems dimensioning objects if
Zoom is not %100.

Regards,
Sandy


-----Original Message-----
Oh... I'm sorry but I don't think I explained my problem
clearly. I have two sheets with checkboxes. On sheet1,
I
have four that I would like to set the checkbox left
property to 50. This is done by the following
procedure,
which is invoked by clicking the command button on
sheet1:

Private Sub CommandButton1_Click()
Dim myObj As OLEObject
For Each myObj In ActiveWorkbook.ActiveSheet.OLEObjects
Debug.Print myObj.Name
If Len(myObj.Name) = 8 Then
If Left(myObj.Name, 8) = "CheckBox" Then
myObj.Left = 50
End If
End If
Next
End Sub

On sheet2, I have a sample of what appears to be
checkbox
controls and I attempted to do the same thing as I did
for
the checkboxes on sheet1 with the following code (also
triggered with a command button on sheet2):

Private Sub CommandButton1_Click()
Dim myObj As OLEObject
For Each myObj In ActiveWorkbook.ActiveSheet.OLEObjects
Debug.Print myObj.Name
If Len(myObj.Name) = 9 Then
If Left(myObj.Name, 9) = "Check Box" Then
myObj.Left = 50
End If
End If
Next
End Sub

Here is the problem... the For Each...Next loop only
finds
one OLE Objects... the command button. I don't
understand
why the checkboxes' names appear in the debug window --
that is, unless they are not an OLEObject. But if that
is
true, then what are they? Why can't I access the
checkbox
properties in design mode?

If anyone has any idea... I'd really appreciate the
help!
I've attached a copy of the excel file to my first
message
of this thread.

Thanks,

Robert

-----Original Message-----
the reason is the procedure only applys to active sheet
so u may make a loop to walk through


.

.