2

I've got a user form that looks like the image below, with a naming convention to identify levels & positions (like the example above the pic). I have the current code below, as well - but I haven't added code for each of the "Port" checkboxes yet, and eventually I'll be inserting/removing a value specific to each checkbox into arrays based on whether they're checked/un-checked.

I'd like to know about the following:

  • Are conditions based on checking or un-checking a checkbox better chosen by reading whether it's checked/un-checked (like I'm doing below), or upon "a change" in ticking?

  • Can I shorten the number or times I code an action for any checkbox on the form to just one (or a few) case(s) instead of coding every one of those (I'll eventually be assigning a value to an array (or removing one) based on what's checked (or un-checked))?

  • Can I take a portion of the name of a checkbox (like the first 4 or 6 characters) as a value to determine what I'm handling if #2 is possible?

I've been staring at this so long I'm a little brain-dead - I'm not sure if I'm asking the right questions here, or what else to ask. Essentially, I want to avoid having to repeat what is pretty much the same thing over and over (like I'm doing now).

Private Sub UserForm_Initialize()
Dim chBox As Control
Dim comboBox As Control
Dim arrFreq() As String
Dim i As Long


    'Use the Split function to create two zero based one dimensional arrays.
    arrFreq = Split("Unused|700|850|1900|2100", "|")
    For Each comboBox In ADSinputform.Controls
        If TypeOf comboBox Is MSForms.comboBox Then
            For i = 0 To UBound(arrFreq)
                'Use .List property to write array data to all the comboBoxes
                comboBox.List = arrFreq
            Next i
        End If
    Next
    'Empty NameTextBox
    SiteNameTextBox.Value = siteName


End Sub

Private Sub AlphaSectCheckbox_Click()

    If AlphaSectCheckbox.Value = True Then AlphaAnt_Frame.Visible = True
    If AlphaSectCheckbox.Value = False Then
        AlphaAnt_Frame.Visible = False
        For Each chBox In AlphaAnt_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub BetaSectCheckbox_Click()

    If BetaSectCheckbox.Value = True Then BetaAnt_Frame.Visible = True
    If BetaSectCheckbox.Value = False Then
        BetaAnt_Frame.Visible = False
        For Each chBox In BetaAnt_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub GammaSectCheckbox_Click()

    If GammaSectCheckbox.Value = True Then GammaAnt_Frame.Visible = True
    If GammaSectCheckbox.Value = False Then
        GammaAnt_Frame.Visible = False
        For Each chBox In GammaAnt_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub DeltaSectCheckbox_Click()

    If DeltaSectCheckbox.Value = True Then DeltaAnt_Frame.Visible = True
    If DeltaSectCheckbox.Value = False Then
        DeltaAnt_Frame.Visible = False
        For Each chBox In DeltaAnt_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub A1Checkbox_Click()

    If A1Checkbox.Value = True Then A1Port_Frame.Visible = True
    If A1Checkbox.Value = False Then
        A1Port_Frame.Visible = False
        For Each chBox In A1Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub A2Checkbox_Click()

    If A2Checkbox.Value = True Then A2Port_Frame.Visible = True
    If A2Checkbox.Value = False Then
        A2Port_Frame.Visible = False
        For Each chBox In A2Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub A3Checkbox_Click()

    If A3Checkbox.Value = True Then A3Port_Frame.Visible = True
    If A3Checkbox.Value = False Then
        A3Port_Frame.Visible = False
        For Each chBox In A3Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub A4Checkbox_Click()

    If A4Checkbox.Value = True Then A4Port_Frame.Visible = True
    If A4Checkbox.Value = False Then
        A4Port_Frame.Visible = False
        For Each chBox In A4Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub A5Checkbox_Click()

    If A5Checkbox.Value = True Then A5Port_Frame.Visible = True
    If A5Checkbox.Value = False Then
        A5Port_Frame.Visible = False
        For Each chBox In A5Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub A6Checkbox_Click()

    If A6Checkbox.Value = True Then A6Port_Frame.Visible = True
    If A6Checkbox.Value = False Then
        A6Port_Frame.Visible = False
        For Each chBox In A6Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub B1Checkbox_Click()

    If B1Checkbox.Value = True Then B1Port_Frame.Visible = True
    If B1Checkbox.Value = False Then
        B1Port_Frame.Visible = False
        For Each chBox In B1Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub B2Checkbox_Click()

    If B2Checkbox.Value = True Then B2Port_Frame.Visible = True
    If B2Checkbox.Value = False Then
        B2Port_Frame.Visible = False
        For Each chBox In B2Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub B3Checkbox_Click()

    If B3Checkbox.Value = True Then B3Port_Frame.Visible = True
    If B3Checkbox.Value = False Then
        B3Port_Frame.Visible = False
        For Each chBox In B3Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub B4Checkbox_Click()

    If B4Checkbox.Value = True Then B4Port_Frame.Visible = True
    If B4Checkbox.Value = False Then
        B4Port_Frame.Visible = False
        For Each chBox In B4Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub B5Checkbox_Click()

    If B5Checkbox.Value = True Then B5Port_Frame.Visible = True
    If B5Checkbox.Value = False Then
        B5Port_Frame.Visible = False
        For Each chBox In B5Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub B6Checkbox_Click()

    If B6Checkbox.Value = True Then B6Port_Frame.Visible = True
    If B6Checkbox.Value = False Then
        B6Port_Frame.Visible = False
        For Each chBox In B6Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub C1Checkbox_Click()

    If C1Checkbox.Value = True Then C1Port_Frame.Visible = True
    If C1Checkbox.Value = False Then
        C1Port_Frame.Visible = False
        For Each chBox In C1Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub C2Checkbox_Click()

    If C2Checkbox.Value = True Then C2Port_Frame.Visible = True
    If C2Checkbox.Value = False Then
        C2Port_Frame.Visible = False
        For Each chBox In C2Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub C3Checkbox_Click()

    If C3Checkbox.Value = True Then C3Port_Frame.Visible = True
    If C3Checkbox.Value = False Then
        C3Port_Frame.Visible = False
        For Each chBox In C3Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub C4Checkbox_Click()

    If C4Checkbox.Value = True Then C4Port_Frame.Visible = True
    If C4Checkbox.Value = False Then
        C4Port_Frame.Visible = False
        For Each chBox In C4Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub C5Checkbox_Click()

    If C5Checkbox.Value = True Then C5Port_Frame.Visible = True
    If C5Checkbox.Value = False Then
        C5Port_Frame.Visible = False
        For Each chBox In C5Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub C6Checkbox_Click()

    If C6Checkbox.Value = True Then C6Port_Frame.Visible = True
    If C6Checkbox.Value = False Then
        C6Port_Frame.Visible = False
        For Each chBox In C6Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub D1Checkbox_Click()

    If D1Checkbox.Value = True Then D1Port_Frame.Visible = True
    If D1Checkbox.Value = False Then
        D1Port_Frame.Visible = False
        For Each chBox In D1Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub D2Checkbox_Click()

    If D2Checkbox.Value = True Then D2Port_Frame.Visible = True
    If D2Checkbox.Value = False Then
        D2Port_Frame.Visible = False
        For Each chBox In D2Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub D3Checkbox_Click()

    If D3Checkbox.Value = True Then D3Port_Frame.Visible = True
    If D3Checkbox.Value = False Then
        D3Port_Frame.Visible = False
        For Each chBox In D3Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub D4Checkbox_Click()

    If D4Checkbox.Value = True Then D4Port_Frame.Visible = True
    If D4Checkbox.Value = False Then
        D4Port_Frame.Visible = False
        For Each chBox In D4Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub D5Checkbox_Click()

    If D5Checkbox.Value = True Then D5Port_Frame.Visible = True
    If D5Checkbox.Value = False Then
        D5Port_Frame.Visible = False
        For Each chBox In D5Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub D6Checkbox_Click()

    If D6Checkbox.Value = True Then D6Port_Frame.Visible = True
    If D6Checkbox.Value = False Then
        D6Port_Frame.Visible = False
        For Each chBox In D6Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Checkbox Naming Convention:

In "Sectors" frame: "AlphaSectCheckbox", "BetaSectCheckbox", "GammaSectCheckbox", "DeltaSectCheckbox" 

In "Antennas" frames: (Alpha) "A1Checkbox", "A2Checkbox", "A3Checkbox", etc / (Beta) "B1Checkbox", "B2Checkbox", "B3Checkbox", etc / (Gamma) "C1Checkbox", "C2Checkbox", "C3Checkbox", etc, etc 

In "Port" frames: (Alpha - Ant 1) "A1P1Checkbox", "A1P2Checkbox", etc / (Alpha - Ant 2) "A2P1Checkbox", "A2P2Checkbox", etc / (Beta - Ant1) "B1P1Checkbox", "B1P2Checkbox", etc / (Beta Ant2) "B2P1Checkbox", "B2P2Checkbox", etc / (Gamma - Ant1) "C1P1Checkbox", "C1P2Checkbox", etc, etc

enter image description here

enter image description here

enter image description here

=========================Edited 1/11/2016 Here's what I implemented after sous2817 answer, this works great, and I can carry on from here with a fraction of the code. This is a new Class Module named "clsUFCheckBox":

Option Explicit

Public WithEvents aCheckBox As msforms.CheckBox

Private Sub aCheckBox_Click()
Dim chBox As Control
Dim actFrmStr As String

     MsgBox aCheckBox.Name & " was clicked" & vbCrLf & vbCrLf & _
        "It refers to the frame: " & aCheckBox.Tag & vbCrLf & vbCrLf & _
        "Its Checked State is currently " & aCheckBox.Value, vbInformation + vbOKOnly, _
        aCheckBox.Name & " & State"

    actFrmStr = aCheckBox.Tag

    If aCheckBox.Value = True Then ADSinputform.Controls(actFrmStr).Visible = True
    If aCheckBox.Value = False Then
        ADSinputform.Controls(actFrmStr).Visible = False
        For Each chBox In ADSinputform.Controls(actFrmStr).Controls
            If TypeOf chBox Is msforms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub
Improve this question
2
  • Would using radio buttons and grouping them work? Commented Nov 30, 2015 at 21:39
  • @sous2817 I'm not sure it would - selecting multiple check boxes is a valid option. Commented Nov 30, 2015 at 21:44

2 Answers 2

Reset to default
3

This is too long for a comment, but hopefully it will get you close (or at least starting towards) a solution.

First, add a class to your project. In this example I called is clsUFCheckBox. Add this following code to the class:

Option Explicit

Public WithEvents aCheckBox As msforms.CheckBox

Private Sub aCheckBox_Click()
     MsgBox aCheckBox.Name & " was clicked" & vbCrLf & vbCrLf & _
        "Its Checked State is currently " & aCheckBox.Value, vbInformation + vbOKOnly, _
        "Check Box # & State"
End Sub

The above creates a click event that will be fired to each checkbox in a collection (that will be coming up shortly). This test code will show you a message box that tells you what check box was clicked and what the current state is. Obviously you'll change this to suit whatever logic you need to solve your current problem, but this will get you started.

Now, in your user form code, add this line at the top (right under your Option Explicit line)

Dim myCheckBoxes() As clsUFCheckBox

This creates an array of the class you created above to hold all of your checkboxes on your form. Now in your Userform_Initialize() event, add this code:

Dim ctl As Object, pointer As Long
ReDim myCheckBoxes(1 To Me.Controls.Count)

    For Each ctl In Me.Controls
        If TypeName(ctl) = "CheckBox" Then
            pointer = pointer + 1
            Set myCheckBoxes(pointer) = New clsUFCheckBox
            Set myCheckBoxes(pointer).aCheckBox = ctl
        End If
    Next ctl

ReDim Preserve myCheckBoxes(1 To pointer)

This code will loop through all of the controls on your userform and add the checkboxes to the array you created above. Also, since you're associating all of the checkboxes to the class you've created, you're giving them a common click event. So each checkbox that you click, runs the same click event.

So there you go. The dozens and dozens of checkboxes you created at the start, they all run the same _click() event. You can put logic in that single event to handle each grouping. The nice thing is, you only have to write the code once and you don't have to add it to each individual checkbox_click event.

If you need a test workbook so you can see it all wired up, let me know and I'll see if I can find a place to upload one for you to review.

Hope that helps. If you get jammed up, please post back and we'll keep hammering at it.

Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

thanks for this! I'll check it out today and get back to you.
@JGR great! Can't wait to hear how it turns out. Good luck!
Sorry this took so long - I had to start working on something else that pulled me away from this project for the last month. One question: This actually works to give me a single point for all checkbox changes, but is there any way to implement the name (i.e."aCheckBox.Name") so that I can show my frame, like e.g. "aCheckBox.Name"& _frame.Visible
@JGR not that I can think of off the top of my head w/o a little bit of work. Getting a reference to the userform could get messy. I'd have to tinker with it a bit to see if I can come up w/ a hacky workaround. I can't promise anything, but I'll see what I can do. Either way, don't wait on me as I'm not sure I'll be able to provide anything that's usable...
@sous2817 Sorted it! I had an error in defining the initialise routine. Thank you for your willingness to help
|
0

You could call a handler function like this where each checkbox simply passes the key string. Then put all your similar code into the handler function which deals with relevant frames 

Private Sub cbA1_Click()
     handleCB ("A1")
End Sub


Private Sub cbA2_Click()
    handleCB ("A2")
End Sub


Public Function handleCB(cb As String)

    Dim formObj As Control
    Set formObj = Me("cb" & cb)
    MsgBox formObj.Name & Chr(10) & formObj.Value

    'do code with the other controls that have the string cb in them
    'such as your frames etc

End Function

Change/click event shouldn't matter too much

Improve this answer

1 Comment

is there a way to do that without putting one on each button?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.