Where does this code go?

[abxy]

I don't know where to insert this code into my UserForm code so that i
works...cause, from my understanding this code should work(but it's no
working), so it must just be in the wrong Private Sub. Here's th
code:

If checkbox1.Value = False And checkbox2.Value = False An
checkbox3.Value = False Then

CommandButton1.Enabled = False

Else

CommandButton1.Enabled = True

End If

and correct me if i'm wrong, but if a checkbox is not enabled, it'
value is considered false, right?

Thanks for your hel

 

[Bob Phillips]

What do you want to trigger this code? Is it clicking on a command button?

Whatever the vent, it must go in the Userform module, not a sheet or
standard code module.

--

HTH

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

 

[abxy]

well, I have 3 checkboxes and 1 command button, and i want to make i
so that If none of the checkboxes are enabled or checked then m
command button won't be enabled...that is, until a checkbox i
checked.

so, what would my code be, and what Private Sub do i put it under

 

[Bob Phillips]

How about this


Private Sub CheckBox1_Click()
If CheckBox1.Value = CheckBox2.Value And CheckBox2.Value =
CheckBox3.Value Then
If CheckBox1.Value = False Then
CommandButton1.Enabled = False
End If
Else
CommandButton1.Enabled = True
End If
End Sub

Private Sub CheckBox2_Click()
If CheckBox1.Value = CheckBox2.Value And CheckBox2.Value =
CheckBox3.Value Then
If CheckBox1.Value = False Then
CommandButton1.Enabled = False
End If
Else
CommandButton1.Enabled = True
End If
End Sub

Private Sub CheckBox3_Click()
If CheckBox1.Value = CheckBox2.Value And CheckBox2.Value =
CheckBox3.Value Then
If CheckBox1.Value = False Then
CommandButton1.Enabled = False
End If
Else
CommandButton1.Enabled = True
End If
End Sub

Private Sub UserForm_Activate()
CommandButton1.Enabled = False
End Sub


--

HTH

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

 

[abxy]

Oh my goodness, this works, this actually works. Thankyou Bob Phillips!
Thankyou!!, and Thankyou even more!! I would have never come up wit
that solution, ever.


...however, by my standards that solution you just gave me is fairl
complex code, and I don't understand everything that's working in orde
for this to work...and that's kind of a problem, becuase my actua
situation has 5 checkboxes not 3...and I tried to alter what you gav
me but it's not coming out right...so could i ask this tiny favor o
you and you show me how the code would look for 5 checkboxes instea
of 3

 

[Bob Phillips]

There's always a catch isn't there<vbg>?

Heres some generic code. It will take any number of checkboxes, just pass
the ones to be checked as an array of controls. So the line
Call CheckStatus(CheckBox1, CheckBox2, CheckBox3, CheckBox4, CheckBox5)
could be changed to
Call CheckStatus(CheckBox1, CheckBox3, CheckBox5)
to only compare those 3 checkboxes.

Ayway here is the code

Option Explicit

Private Sub CheckBox1_Click()
Call CheckStatus(CheckBox1, CheckBox2, CheckBox3, CheckBox4, CheckBox5)
End Sub

Private Sub CheckBox2_Click()
Call CheckStatus(CheckBox1, CheckBox2, CheckBox3, CheckBox4, CheckBox5)
End Sub

Private Sub CheckBox3_Click()
Call CheckStatus(CheckBox1, CheckBox2, CheckBox3, CheckBox4, CheckBox5)
End Sub

Private Sub CheckBox4_Click()
Call CheckStatus(CheckBox1, CheckBox2, CheckBox3, CheckBox4, CheckBox5)
End Sub

Private Sub CheckBox5_Click()
Call CheckStatus(CheckBox1, CheckBox2, CheckBox3, CheckBox4, CheckBox5)
End Sub

Private Sub CheckStatus(ParamArray pControls() As Variant)
Dim fSame As Boolean
Dim i As Long

If LBound(pControls) <> UBound(pControls) Then
fSame = True
For i = LBound(pControls) + 1 To UBound(pControls)
If pControls(0).Value <> pControls(i).Value Then
fSame = False
Exit For
End If
Next i

CommandButton1.Enabled = Not fSame Or pControls(0).Value = True
End If

End Sub

Private Sub UserForm_Activate()
CommandButton1.Enabled = False
End Sub


--

HTH

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