Looping through textboxes

I am trying to loop through a group of textboxes that are on an excel sheet
(not in a userform) to see if they have been checked or not. How can I do
this. Below is what I have now.

Dim Ctrl As CheckBox

For Each Ctrl In Sheets("Program").CheckBoxes
If Sheets("Program").CheckBoxFx = True Then
FDirect = "FsX"
Else
If Sheets("Program").CheckBoxFy = True Then
FDirect = "FsY"
Else
If Sheets("Program").CheckBoxFz = True Then
FDirect = "FsZ"
End If
End If
End If
Next Ctrl

I'm not sure why you want to loop through the checkboxes. It looks like you
just want to check those 3 checkboxes.

I used 3 checkboxes from the Forms toolbar and this worked ok:

Option Explicit
Sub testme01()

Dim FDirect As String

FDirect = ""

With Worksheets("Program")
If .CheckBoxes("CheckBoxFx").Value = xlOn Then
FDirect = "FsX"
Else
If .CheckBoxes("CheckBoxFy").Value = xlOn Then
FDirect = "FsY"
Else
If .CheckBoxes("CheckBoxFz").Value = xlOn Then
FDirect = "FsZ"
End If
End If
End If
End With

MsgBox FDirect

End Sub

If I used a checkbox from the Control Toolbox toolbar, this worked:

Option Explicit
Sub testme02()

Dim FDirect As String

FDirect = ""

With Worksheets("Program")
If .CheckBoxFx.Value = True Then
FDirect = "FsX"
Else
If .CheckBoxFy.Value = True Then
FDirect = "FsY"
Else
If .CheckBoxFz.Value = True Then
FDirect = "FsZ"
End If
End If
End If
End With

MsgBox FDirect

End Sub

==================
That said, if you're trying to let the user choose one option from a few, you
may want to use Optionbuttons. And there are a couple of different types of
those, too--one on the Forms toolbar and one on the Control toolbox toolbar.

Be sure to include the type you used in any followup question.


CLamar wrote:
>
> I am trying to loop through a group of textboxes that are on an excel sheet
> (not in a userform) to see if they have been checked or not. How can I do
> this. Below is what I have now.
>
> Dim Ctrl As CheckBox
>
> For Each Ctrl In Sheets("Program").CheckBoxes
> If Sheets("Program").CheckBoxFx = True Then
> FDirect = "FsX"
> Else
> If Sheets("Program").CheckBoxFy = True Then
> FDirect = "FsY"
> Else
> If Sheets("Program").CheckBoxFz = True Then
> FDirect = "FsZ"
> End If
> End If
> End If
> Next Ctrl

--

Dave Peterson