PDA

View Full Version : VBA Excel Userform Multiple Condition Stump



samkhazary
11-17-2008, 03:41 PM
Hey everyone, I'm pretty new to the forum so sorry if I leave some details out.

I have created this user form. What is driving me crazy is coding it for multiple conditions.

I've gotten it so that if you were you check off the "Hide Equities Category" you would automatically check off "Hide U.S. Equities Category" and "Hide International Equities Category." But now my issue is that I need it to do the following:

If you were to un-check "Hide Equities Category" you would automatically un-check off "Hide U.S. Equities Category" and "Hide International Equities Category."

If you were to check check off both the "Hide U.S. Equities Category" and "Hide International Equities Category" you would automatically check the "Hide Equities Category."

If you were to check check off the "Hide U.S. Equities Category" and but un-check the "Hide International Equities Category" you would not be able to check the "Hide Equities Category."

If you were to check check off the "Hide International Equities Category" but un-check the "Hide U.S. Equities Category" you would not be able to check the "Hide Equities Category."

Hopefully this paints the picture or what I'm trying to do clearly. You can't have the "Hide Equities Category" checked off if either the "Hide U.S. Equities Category" and "Hide International Equities Category" are not checked and vice versa.

Any help would be greatly appreciated.

Also at the end of the project I'd like the command button to submit all the selected choices, but not clear them so you can continue to pull up the options screen and make adjustment in a project.

Bob Phillips
11-17-2008, 04:20 PM
Try code like this



Option Explicit

Private fReEntry As Boolean

Private Sub CheckBoxHideEquities_Click()

If Not fReEntry Then

fReEntry = True

With Me

.CheckBoxHideUSEquities.Value = .CheckBoxHideEquities.Value
.CheckBoxHideInternationalEquities.Value = .CheckBoxHideEquities.Value
End With

fReEntry = False
End If
End Sub

Private Sub CheckBoxHideUSEquities_Click()

If Not fReEntry Then

fReEntry = True

With Me

If .CheckBoxHideUSEquities.Value = .CheckBoxHideInternationalEquities.Value Then

.CheckBoxHideEquities.Value = .CheckBoxHideUSEquities.Value
End If
End With

fReEntry = False
End If
End Sub

Private Sub CheckBoxHideInternationalEquities_Click()

If Not fReEntry Then

fReEntry = True

With Me

If .CheckBoxHideUSEquities.Value = .CheckBoxHideInternationalEquities.Value Then

.CheckBoxHideEquities.Value = .CheckBoxHideInternationalEquities.Value
End If
End With

fReEntry = False
End If
End Sub

samkhazary
11-17-2008, 06:01 PM
Heyyy thank you very much for the speedy response. One problem I'm having is that how do I get it to uncheck the "Hide Equities Category" if only either
"Hide U.S. Equities Category" or "Hide International Equities Category" are checked?

mikerickson
11-17-2008, 06:56 PM
Perhaps this will work for you.
Dim ufEventsDisabled As Boolean

Private Sub CheckBoxHideEquities_Click()
If ufEventsDisabled Then Exit Sub
ufEventsDisabled = True
With Me.CheckBoxHideEquities
Me.CheckBoxHideUSEquities.Value = .Value
Me.CheckBoxHideInternationalEquities.Value = .Value
End With
ufEventsDisabled = False
End Sub

Private Sub CheckBoxHideUSEquities_Click()
If ufEventsDisabled Then Exit Sub
ufEventsDisabled = True
With Me.CheckBoxHideEquities
.Enabled = Me.CheckBoxHideUSEquities.Value Eqv Me.CheckBoxHideInternationalEquities.Value
.Value = Me.CheckBoxHideUSEquities.Value And Me.CheckBoxHideInternationalEquities.Value
End With
ufEventsDisabled = False
End Sub

Private Sub CheckBoxHideInternationalEquities_Click()
If ufEventsDisabled Then Exit Sub
ufEventsDisabled = True
With Me.CheckBoxHideEquities
.Enabled = Me.CheckBoxHideUSEquities.Value Eqv Me.CheckBoxHideInternationalEquities.Value
.Value = Me.CheckBoxHideUSEquities.Value And Me.CheckBoxHideInternationalEquities.Value
End With
ufEventsDisabled = False
End Sub

samkhazary
11-17-2008, 08:31 PM
Mike, thank you for your response. does this code just go into the userform code section? how would I apply it now to the rest of the check marks with the same idea.

mikerickson
11-17-2008, 11:01 PM
In the attachment, I put the code at the start of UseformAATemplateOptions' code module.

I also added another userform to show how mult-checkboxes can be linked like that.

The logic of the checkbox_All is that all other checkboxes get the value of checkbox_All.

Each of the other checkboxes call the same routine, which
1) sets the .Enabled property of checkbox_All to True only if all secondary checkboxes have the same value.
The Eqv operator is used for this.

then

2) checkbox_All.Value = And(all other checkbox values)

samkhazary
11-18-2008, 03:32 PM
So I think I got this setup up correctly for the most part.

Code works perfect when it only deals with two variables.

In the fixed income and alternatives column it seems to act quirky when more than two variables are introduced.

Problems:
1) If you check "Hide Investment Grade Bonds" the "Hide U.S. fixed Income Category" isn't greyed out, but if you choose two options in that heading it is greyed out. It should be greyed out if not all three are selected.

2) If you check every option under the U.S. and Non-U.S. column it checks off both headers, but that doesn't automatically check off the maser "Hide Fixed Income Category."

Same applies for the Alternatives category.

Any suggestions?

mikerickson
11-18-2008, 06:13 PM
The second column works the way you want it.

In that column you have one Grand Master button, two subMaster buttons and a two groups of sub-sub-buttons, one group with two sub-sub-buttons and one with three. (I am not going to even try to spell those long control names correctly.)

Editing is much easier if you have all the buttons at the same level call the same routine, hence the Sub USFi_EnabledControl() which is called by all three of the US fi buttons.

Also when the sub-sub-buttons change the enabled/value of the sub-buttons, that has to be carried up to the master.

mikerickson
11-19-2008, 12:59 AM
This version is superior to the previous one.
Each of the checkboxes is in a class clsSubordinateCheckBox, with properties:
.chkBox (WithEvents)
.Subordinates (class)
.Sisters (array)
.Mother

The chkBox_Click event passes the value of a checkbox to its subordinates. It also sets Mother's enabled property depending on the value of its Sisters.

The class requires that there be one top checkbox. I added that to the userform. Currently it is visible. Un-commenting out the indicated line in the Userform Initialize routine will make its Visbile = False.

The userform's Initialize event puts the check boxes in the proper relationship with each other.
First one declares which checkboxes are immediate subordinates of the TopBox.
Then one sets the subordinates of each of the level one check boxes.
Etc.

Also, note and preserve the Public Boolean variable ufEventsDisabled in a normal module.

I hope this helps.

samkhazary
11-19-2008, 07:24 AM
Hey Mike, I appreciate all your help, thank you!

I seem to be running into the same problem as before.

I can't seem to get the level 2 daughters that fall under the "CheckBoxHideUSFI" and "CheckBoxNonUSFI" to work properly. When any of the level 2 options are clicked they set off the master check box "CheckBoxHideFI."

Any ideas? It seem's the be the only column I keep having issues with.

mikerickson
11-19-2008, 08:07 AM
I'm not sure what the problem is.

When the userform is shown, all checkboxes are unchecked, and enabled.
I check Hide Short Term Investment Grade.
Both Hide US Fixed Income Category and Hide Fixed Income Category are disabled.
Which seems appropriate.

What is happening with your testing and what do you want it to do?

samkhazary
11-19-2008, 08:09 AM
Correct but it then enables the "Hide Fixed Income Category" which should also be greyed out unless all options are checked.

mikerickson
11-19-2008, 08:16 AM
Hide Short Term Investment Grade is checked.
Hide Fixed Income Category is disabled.
Hide US Fixed Income Category is disabled.

What causes Hide Fixed Income Category to become enabled?


Edit; I Found IT.
Checking and then unchecking, Hide International Bonds Category causes Hide Fixed Income Category to become in appropriately enabled.

Let me get back to you.

mikerickson
11-19-2008, 08:28 AM
Change the Click event in the class module to
Private Sub ChkBox_Click()
Dim oneSubordinate As Variant
Dim workingMother As clsSubordinateCheckBox
Dim myVal As Boolean, ableMem As Boolean
myVal = Value
ableMem = ufEventsDisabled

Rem pass enabled upwards
Mother.Enabled = SistersAllSame And SistersAllEnabled

Rem pass value downwards
If Not ufEventsDisabled Then
For Each oneSubordinate In Daughters
oneSubordinate.Value = myVal
Next oneSubordinate

Rem correct value upward
ufEventsDisabled = True
Set workingMother = Mother
Do
With workingMother
.Value = myVal And (.Enabled)

Set workingMother = .Mother
End With
Loop Until workingMother.Mother.Name = workingMother.Name

With workingMother: Rem set topBox
.Value = .Daughters(1).AndOfSisters()
.Enabled = .Enabled And .Daughters(1).SistersAllSame()
End With

ufEventsDisabled = ableMem
End If
End Sub
Add this function to the class module
Function SistersAllEnabled()
Dim oneSis As Variant
SistersAllEnabled = True
For Each oneSis In Sisters
SistersAllEnabled = SistersAllEnabled And oneSis.Enabled
Next oneSis
End Function

samkhazary
11-19-2008, 01:03 PM
I attached the updated code. Maybe I did something wrong, and sorry if I did, I just started working with VBA at the beginning of this month, and appreciate all your help.

Its seems to still check off the master category "Hide Fixed Income Category" if you check off:
Hide Investment Grade Bonds CategoryHide
Short-Term Investment Grade Bonds Category
Hide High Yield Bonds Category
Hide International Bonds Category
Hide Emerging Market Bonds Category

Which I'm assuming are the level 2 daughters... I attached the sheet with the updated code. Again thank you!

mikerickson
11-19-2008, 07:29 PM
This is the way that the boxes in that column are subortinated (using ... for indenting.)

Hide Fixed Income Category
...Hide US Fixed Income Category
......Hide Investment Grade Bonds Category
......Hide Short Term Investment Grade
......Hide High Yield Bonds Category

...Hide Non-US Fixed Income Category
......Hide International Bonds Category
......Hide Emerging Market Bonds Category


It is set up so that if all of the daughters are checked, then the mother will become enabled AND checked.

Checking the five boxes you mentioned will enable and check their mothers (US Fixed and Non-US Fixed).
Since those are the only two daughters of Hide Fixed Income Category and since they are both true, Hide Fixed Income Category also gets checked.

A Mother will be enabled and True, if and only if all her Daughters are True.
A Mother will be enabled and False, if and only if all her Daughters are False.
A Mother will be disabled and False in all other cases.

This will hold even when the Mother is also a Daughter (of some other box) or if the Daughters are Mothers of their own grand-Daughters.
Thus changing a grand-Daughter can effect the enabled/value of the grand-Mother.

Your implementation was correct. But, after thinking about this today, I tightened up the code by removing the Sisters.
Attached is the updated version.

samkhazary
11-21-2008, 10:16 AM
Mike, works perfect!

Thank you so much!