Loop for checkboxes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible in VBA to do a typical loop? Such as this. Basically I want checkboxes 2-5 to be invisible if checkbox 1 is not checked. I've found that it will not allow me to put in the "x + 1". Thanks. Matt

const x =
If checkbox1.value = fals
do while x <
checkbox(x).visible = fals
x +
loo
end if
 
What sort of checkboxes, userform, forms toolbar, or control toolbar?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Matt said:
Is it possible in VBA to do a typical loop? Such as this. Basically I
Click to expand...
want checkboxes 2-5 to be invisible if checkbox 1 is not checked. I've
found that it will not allow me to put in the "x + 1". Thanks. Matt.
 
I'd like to know how to do this on a Userform.
-----Original Message-----
What sort of checkboxes, userform, forms toolbar, or control toolbar?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

this. Basically I
want checkboxes 2-5 to be invisible if checkbox 1 is not checked. I've
found that it will not allow me to put in the "x + 1". Thanks. Matt.


.
Click to expand...
 
Hi Matt,

Something like this should work:

Private Sub CheckBox1_Click()
Dim ole As OLEObject

For Each ole In OLEObjects
If TypeOf ole.Object Is MSForms.CheckBox Then
If ole.Name <> "CheckBox1" Then
ole.Visible = CheckBox1.Value
End If
End If
Next ole
End Sub

It loops through all OLEObjects, and if the current OLEObject in the loop is
a CheckBox, it will set its Visible property according to the value of
CheckBox1.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
Jake, thanks. This helps, but I forgot to mention that I have other control checkboxes on the worksheet that I don't want to be dependent on checkbox1. I just want a selection dependent on the first checkbox. Any ideas? Thanks. Matt
 
Matt,

If your CheckBoxes are named "CheckBox1", "CheckBox2", and so on, you can
use this:

Private Sub CheckBox1_Click()
Dim ole As OLEObject
Dim nChkNum As Integer

For Each ole In OLEObjects
If TypeOf ole.Object Is MSForms.CheckBox Then
nChkNum = CInt(Mid$(ole.Name, Len("CheckBox") + 1))
If nChkNum >= 2 And nChkNum <= 5 Then
ole.Visible = CheckBox1.Value
End If
End If
Next ole
End Sub

If not, you will have to change the If statement to something like this:

If ole.Name="Mychk2" Or ole.Name="check3" Or ole.Name="checkbox04" Then

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
Matt,

Please try to keep a question in the same thread. I didn't see your thread
on this subject posted 1 1/2 hours prior to this one (and those answering
your other thread probably didn't see this one), so a lot of duplicated work
resulted.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
Rob,

This is the sort of thing that you want

Dim ctl As MSForms.Control

For Each ctl In Me.Controls
If TypeName(ctl) = "CheckBox" Then
If ctl.Value <> True Then
ctl.Visible = False
End If
End If
Next ctl


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top