Form stuff - Array of Checkboxes to make the code cleaner?

[MYX]

Hey all,
I have been searching and reading endlessly trying to find something similar, but can't seem to find it. I have a application I designed to interface with After Effects. It is for title graphics (video) at events where we have various speakers and titles and locations where they are from. The problem stems from the fact that the name, title, and location may need to be in different places on the screen at a given time.
So, I used a simple form which has drop downs (comboboxes) for the selection of the name, title, and location. There is more, but I am keeping the description to this as to not muddy things more than they need to be. Under each combo, there are 3 check boxes. Each checkbox designates a location on the screen... (Upper, Lower, and Misc)

If the user selects bob smith (from the combo) and selects the "Upper" checkbox I programmaticly alter the other "upper" checkboxes to be a different color, disabled, and locked. This is to keep the user from possibly trying to put two pieces of information into the same section of the screen.

The same works for Lower and Misc.

This all works rather well minus a couple of logistical things I need to work out. The problem (and this happens with most creative types) is that as I modify the look, or layout, or add extra features, I have to modify a brazillion nuggets of code as there is a function for each checkbox. I would like to put the specific checkboxes into an array so that only the items in that particular array are effected when they click on a checkbox. It could loop through these particular checkboxes and only change their settings, not all the checkboxes in the whole form (which there is loads of source code for). I have tried this a number of ways, but can not seem to make it work.

Here is a simplified bit of the current working code...

Private Sub Sec_Upper_Click()
If Sec_Upper.Value = True Then
    Sec_Lower.SpecialEffect = fmButtonEffectFlat
    Sec_Lower.ForeColor = &H808080
    Sec_Lower.Enabled = False
    Sec_Lower.Locked = True
'    Sec_Misc.BackStyle = fmBackStyleTransparent
    Sec_Misc.SpecialEffect = fmButtonEffectFlat
    Sec_Misc.ForeColor = &H808080
    Sec_Misc.Enabled = False
    Sec_Misc.Locked = True
'    Misc_Upper.BackStyle = fmBackStyleTransparent
    Misc_Upper.SpecialEffect = fmButtonEffectFlat
    Misc_Upper.ForeColor = &H808080
    Misc_Upper.Enabled = False
    Misc_Upper.Locked = True
'    Cust_Upper.BackStyle = fmBackStyleTransparent
    Cust_Upper.SpecialEffect = fmButtonEffectFlat
    Cust_Upper.ForeColor = &H808080
    Cust_Upper.Enabled = False
    Cust_Upper.Locked = True
'    Atl_Upper.BackStyle = fmBackStyleTransparent
    Atl_Upper.SpecialEffect = fmButtonEffectFlat
    Atl_Upper.ForeColor = &H808080
    Atl_Upper.Enabled = False
    Atl_Upper.Locked = True
'    PIP_Upper.BackStyle = fmBackStyleTransparent
    PIP_Upper.SpecialEffect = fmButtonEffectFlat
    PIP_Upper.ForeColor = &H808080
    PIP_Upper.Enabled = False
    PIP_Upper.Locked = True

        ElseIf Sec_Upper.Value = False Then
'                Sec_Upper.BackStyle = fmBackStyleOpaque
                Sec_Upper.SpecialEffect = fmButtonEffectSunken
                Sec_Upper.ForeColor = &HFFFFFF
                Sec_Upper.Enabled = True
                Sec_Upper.Locked = False
'                Sec_Lower.BackStyle = fmBackStyleOpaque
                Sec_Lower.SpecialEffect = fmButtonEffectSunken
                Sec_Lower.ForeColor = &HFFFFFF
                Sec_Lower.Enabled = True
                Sec_Lower.Locked = False
'                Sec_Misc.BackStyle = fmBackStyleOpaque
                Sec_Misc.SpecialEffect = fmButtonEffectSunken
                Sec_Misc.ForeColor = &HFFFFFF
                Sec_Misc.Enabled = True
                Sec_Misc.Locked = False
'                Misc_Upper.BackStyle = fmBackStyleOpaque
                Misc_Upper.SpecialEffect = fmButtonEffectSunken
                Misc_Upper.ForeColor = &HFFFFFF
                Misc_Upper.Enabled = True
                Misc_Upper.Locked = False
'                Cust_Upper.BackStyle = fmBackStyleOpaque
                Cust_Upper.SpecialEffect = fmButtonEffectSunken
                Cust_Upper.ForeColor = &HFFFFFF
                Cust_Upper.Enabled = True
                Cust_Upper.Locked = False
'                Atl_Upper.BackStyle = fmBackStyleOpaque
                Atl_Upper.SpecialEffect = fmButtonEffectSunken
                Atl_Upper.ForeColor = &HFFFFFF
                Atl_Upper.Enabled = True
                Atl_Upper.Locked = False
'                PIP_Upper.BackStyle = fmBackStyleOpaque
                PIP_Upper.SpecialEffect = fmButtonEffectSunken
                PIP_Upper.ForeColor = &HFFFFFF
                PIP_Upper.Enabled = True
                PIP_Upper.Locked = False
End If
End Sub

Here is what I am trying to do...

Private Sub Sec_Upper_Click()
Dim chBoxArray(6) As CheckBox
Set chBoxArray(0) = Sec_Upper
Set chBoxArray(1) = Sec_Lower
Set chBoxArray(2) = Sec_Misc
Set chBoxArray(3) = Misc_Upper
Set chBoxArray(4) = Cust_Upper
Set chBoxArray(5) = Atl_Upper
Set chBoxArray(6) = PIP_Upper

If Sec_Upper.Value = True Then
Dim x As Integer
For x = 1 To 6
    chBoxArray(x).BackStyle = fmBackStyleTransparent
    chBoxArray(x).SpecialEffect = fmButtonEffectFlat
    chBoxArray(x).ForeColor = &H808080
    chBoxArray(x).Enabled = False
    chBoxArray(x).Locked = True
    Next x

    ElseIf Sec_Upper.Value = False Then
        Dim y As Integer
        For y = 0 To 6
        chBoxArray(y).BackStyle = fmBackStyleOpaque
        chBoxArray(y).SpecialEffect = fmButtonEffectSunken
        chBoxArray(y).ForeColor = &HFFFFFF
        chBoxArray(y).Enabled = True
        chBoxArray(y).Locked = False
        Next y
End If

End Sub

Any thoughts?

 

[tlowry]

How about an array of check box names instead.

I'm not sure what this does, but it doesn't crash.

Sub Sec_Upper_Click()
    Dim chboxes, x, y
    With Me
        chboxes = Array(.Sec_Upper, .Sec_Lower, .Sec_Misc, .Misc_Upper, .Cust_Upper, .Atl_Upper, .PIP_Upper)
    End With
    Select Case Sec_Upper.Value
        Case True
            For x = 1 To 6
                With chboxes(x)
                    .BackStyle = fmBackStyleTransparent
                    .SpecialEffect = fmButtonEffectFlat
                    .ForeColor = &H808080
                    .Enabled = False
                    .Locked = True
                End With
            Next x
        Case False
            For y = 0 To 6
                With chboxes(y)
                    .BackStyle = fmBackStyleOpaque
                    .SpecialEffect = fmButtonEffectSunken
                    .ForeColor = &HFFFFFF
                    .Enabled = True
                    .Locked = False
                End With
            Next y
    End Select
End Sub

 

[MYX]

Grrrr! That is exactly it. It works great. I had tried to load the array that way as that is how I would have done it in C (edit: the layout, not the syntax) , but I got all sorts of errors on that. I had no leading periods.

are the leading periods because the checkbox is a child of the form?

I am also curious why you used a switch case over the if then.

Great job. Thank you!!!

 

[GTO]

Hi there,

Not well tested, and at least in 2000, the helpfile doesn't say that GroupName applies to checkboxes, but appears to work... If not that, I think you could use the .Tag property. Anyways, as it appears that we are wanting similar things to happen (to checkboxes other than the one clicked), maybe a small class module for the event?

Again, not well tested, but seems to work:

In a new Class Module named: clsCheckBoxes

Option Explicit
    
Public WithEvents oCheckBox As MSForms.CheckBox
Public WithEvents oUserForm As MSForms.UserForm
    
Property Set OneCheckBox(ThisCheckbox As MSForms.CheckBox)
    Set oCheckBox = ThisCheckbox
End Property
    
Property Set MyForm(ThisForm As UserForm)
    Set oUserForm = ThisForm
End Property
    
Private Sub oCheckBox_Click()
Dim ctl         As Control
Dim sGrpName    As String
    
    sGrpName = oCheckBox.GroupName
    
    For Each ctl In oUserForm.Controls
        If TypeOf ctl Is MSForms.CheckBox Then
            If ctl.GroupName = sGrpName And Not ctl.Name = oCheckBox.Name Then
            
                If oCheckBox.Value Then
                    ctl.Enabled = False
                Else
                    ctl.Enabled = True
                End If
            End If
        End If
    Next
End Sub

In the UserForm, I just added five checkboxes. The first three with the .GroupName of "Group1" and the last two w/the .GroupName of "Group2".

In the UserForm's Module:

Option Explicit
    
Private chkbx() As clsCheckBoxes
    
Private Sub CommandButton1_Click()
Dim ctl As Control
    
    For Each ctl In Me.Controls
        If TypeOf ctl Is MSForms.CheckBox Then
            MsgBox ctl.GroupName
        End If
    Next
End Sub
    
Private Sub UserForm_Initialize()
Dim ctl As Control
Dim lCount As Long
    
    '// Get a count of checkboxes   //
    For Each ctl In Me.Controls
        If TypeOf ctl Is MSForms.CheckBox Then
            lCount = lCount + 1
        End If
    Next
    '// Resize to how many instances of the class we'll need.   //
    ReDim chkbx(0 To lCount - 1)
    
    '// I happen to choose a zero-base.  Loop back through the controls,    //
    '// setting a reference to a new instance of the class for each         //
    '// checkbox.                                                           //
    lCount = -1
    For Each ctl In Me.Controls
        If TypeOf ctl Is MSForms.CheckBox Then
            lCount = lCount + 1
            Set chkbx(lCount) = New clsCheckBoxes
            Set chkbx(lCount).OneCheckBox = ctl
            Set chkbx(lCount).MyForm = Me
        End If
    Next
End Sub

I didn't test for properties other than .Enabled. Sometimes a particular property will not be available in the class as it would in the form's object module. Afraid I'm not actually sure why that exactly is

Hope that helps,

Mark

 

[MYX]

Sorry, I wasn't clear on my last post... What tlowry had written worked great.
I was saying that when I tried a way similar to his I got all sorts of errors.
I am using 2003.

 

[tlowry]

are the leading periods because the checkbox is a child of the form?

Yes, it saves alot of typing. It uses the "With obj" in front of the .

I am also curious why you used a switch case over the if then.

Long and/or complicated If's give me a headache
Select Case statements are easier to maintain
The code is more symmetrical

 

[GTO]

Ahhh... Okay, I am glad you got a great answer and more manageable code I was just showing another possible solution to consider.

Mark

 

[MYX]

Cool. Thank you both for your help!