Save userform checkbox selections to array (to replicate on userform)

Question

How do I identify all the selected checkboxes in a frame in a user form and assign them to an array so that I can either/both: duplicate the selected checkboxes in one or more other frames & use the array to fill out spreadsheet cells?

Kind of a two-part question, but I think they go hand-in-hand (I'm not sure). I have a userform with multiple frames, and a lot of checkboxes inside each (SS) - I have a standard naming convention for all of them (explained bottom).

I will need to identify which checkboxes are selected (and the selection in the comboboxes) so I can put all that into a spreadsheet. I also will need the option for the user to copy all the selected checkboxes (and the selection in the comboboxes) from one frame to one to three of the other frames if s/he wants to. I have a "Copy" button that initializes a short userform to select which frame to copy from and which frame(s) to copy to. (For example: the ability to copy all the selections from the "Alpha Antennas" frame to one or more of "Beta Antennas" frame, "Gamma Antennas" frame, "Delta Antennas" frame.) Really stuck on what to do in the main form once I get that? I think one array will get me the two functions I need (copying one frame to another and filling out the spreadsheet) - but I don't know the next step. Any help?

Some code/naming/SS:

The command button that loads the main form:

Sub CreateADS()
Dim oneForm As Object 
'==========================================================
'On Error GoTo ErrHandler 'Trying to catch errors - will input more down there, later

    ADSinputform.Show
    For Each oneForm In UserForms
        Unload oneForm
        'Unload ADSinputform
    Next oneForm
End Sub

The main Userform beginning code:

    Dim myCheckBoxes() As clsUFCheckBox

    Private Sub UserForm_Activate()
        '======================================================
        'couple pre-initialization things here
        '======================================================
    End Sub

    Private Sub UserForm_Initialize()
    Dim chBox As Control
    Dim comboBox As Control
    Dim arrFreq() As String
    Dim i As Long
    Dim siteName As String
    Dim ctrl As Object, pointer As Long

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

    ReDim Preserve myCheckBoxes(1 To pointer)

        'Use the Split function to create two zero based one dimensional arrays.
        arrFreq = Split("Unused|GSM,850|GSM,1900|UMTS,850|UMTS,1900|CDMA,850|LTE,700|LTE,850|LTE,1900|LTE,2100|LTE,2300", "|")
        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

        MsgBox "This pops up at the end of initialization"
    End Sub

Private Sub cmdCopy_Click()
Dim chkBox As Control
Dim cmbBox As Control
Dim frmSource As MSForms.Frame
'Dim frmSource As String
Dim valSectCopy1 As String 'to validate that a sector is filled in
Dim valSectCopy2 As String 'to validate that an antenna is filled in
Dim valPortCopy As String 'to validate that a port is filled in

Set frmSource = SectorsFrame
valSectCopy1 = ""
valSectCopy2 = ""
valPortCopy = ""


    For Each chkBox In frmSource.Controls 'Sector-level frame
        If TypeName(chkBox) = "CheckBox" And chkBox.Value = True Then
            valSectCopy1 = chkBox.Tag
            valSectCopy2 = valSectCopy1
            Set frmSource = Controls(valSectCopy1)
            Exit For
        End If
    Next chkBox
    If valSectCopy1 <> "" Then
        For Each chkBox In frmSource.Controls 'Antenna-level frame
            If TypeName(chkBox) = "CheckBox" And chkBox.Value = True Then
                valSectCopy2 = chkBox.Tag
                valPortCopy = valSectCopy2
                Set frmSource = Controls(valSectCopy2)
                Exit For
            End If
        Next chkBox
    Else
        GoTo NoSource
    End If
    If valSectCopy2 <> valSectCopy1 Then
        For Each cmbBox In frmSource.Controls 'Port-level frame
            If TypeName(cmbBox) = "ComboBox" And cmbBox.Value <> "Frequency" Then
                valPortCopy = cmbBox.Value
                Exit For
            End If
        Next cmbBox
    Else
        GoTo NoSource
    End If
    If valSectCopy2 = valPortCopy Then
        GoTo NoSource
    End If



    CopySector.Show
        If CopySector.destSectCopy <> "" And CopySector.srcSectCopy <> "" Then
            MsgBox "Copying the " & CopySector.srcSectCopy & _
                " sector to " & CopySector.destSectCopy & " sector(s)."
            Unload CopySector
            Exit Sub
        Else
            Exit Sub
        End If

NoSource:
    MsgBox "You have not filled in a sector to copy." & vbCrLf & _
        "Please fill out sector info for at least one sector and try again."
    Exit Sub


    End Sub

The questionnaire userform code:

Public srcSectCopy As String
Public destSectCopy As String


Private Sub cmdCopy_Click()
Dim optBtn As Control
Dim chkBox As Control

srcSectCopy = ""
destSectCopy = ""

    For Each optBtn In Me.Controls
        If TypeName(optBtn) = "OptionButton" Then
            If optBtn.Value = True Then
                srcSectCopy = optBtn.Tag
            End If
        End If
    Next optBtn

    If srcSectCopy = "" Then
        MsgBox "You have not selected a sector to copy." & vbCrLf & _
            "Please select a sector to copy from and try again."
        Exit Sub
    End If

    For Each chkBox In Me.Controls
        If TypeName(chkBox) = "CheckBox" Then
            If chkBox.Value = True Then
                If destSectCopy = "" Then
                    destSectCopy = chkBox.Tag
                Else
                    destSectCopy = destSectCopy & ", " & chkBox.Tag
                End If
            End If
        End If
    Next chkBox

    If destSectCopy = "" Then
        MsgBox "You have not selected any sectors to copy to." & vbCrLf & _
            "Please select one or more sectors to be duplicated and try again."
        Exit Sub
    End If

    Msg = "this will copy the " & srcSectCopy & _
        " sector to " & destSectCopy & " sector(s)." & vbCrLf & _
        "Do you want to continue with the operation?"
        Ans = MsgBox(Msg, vbQuestion + vbYesNo)
        Select Case Ans
            Case vbYes
                Me.Hide
            Case vbNo
                Exit Sub
          End Select


End Sub

Private Sub UserForm_Initialize()

End Sub



Private Sub AlphaSect_OptBtn_Change()

    Select Case (AlphaSect_OptBtn.Value)
        Case True:  AlphaSect_CheckBox.Enabled = False
        AlphaSect_CheckBox.Value = False
        Case False: AlphaSect_CheckBox.Enabled = True
    End Select

End Sub

Private Sub BetaSect_OptBtn_Change()

    Select Case (BetaSect_OptBtn.Value)
        Case True:  BetaSect_CheckBox.Enabled = False
        BetaSect_CheckBox.Value = False
        Case False: BetaSect_CheckBox.Enabled = True
    End Select

End Sub

Private Sub GammaSect_OptBtn_Change()

    Select Case (GammaSect_OptBtn.Value)
        Case True:  GammaSect_CheckBox.Enabled = False
        GammaSect_CheckBox.Value = False
        Case False: GammaSect_CheckBox.Enabled = True
    End Select

End Sub

Private Sub DeltaSect_OptBtn_Change()

    Select Case (DeltaSect_OptBtn.Value)
        Case True:  DeltaSect_CheckBox.Enabled = False
        DeltaSect_CheckBox.Value = False
        Case False: DeltaSect_CheckBox.Enabled = True
    End Select

End Sub


Private Sub cmdCancel_Click()

  Msg = "Are you sure you want to cancel and exit without copying?"
        Ans = MsgBox(Msg, vbQuestion + vbYesNo)
        Select Case Ans
            Case vbYes
                Me.Hide
                Unload Me
            Case vbNo
                Exit Sub
          End Select

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then
    ' user clicked the X button
    ' cancel unloading the form, use close button procedure instead
    Cancel = True
    cmdCancel_Click
  End If
End Sub

The following class:

Option Explicit
Public WithEvents aCheckBox As MSForms.CheckBox
Private Sub aCheckBox_Click()
Dim chBox As Control
Dim chBoxTag As String

chBoxTag = aCheckBox.Tag

If Right(aCheckBox.Parent.Name, 10) = "Port_Frame" Then
    If aCheckBox.Value = True Then ADSinputform.Controls(chBoxTag).Enabled = True
    If aCheckBox.Value = False Then
        ADSinputform.Controls(chBoxTag).Enabled = False
    End If
Else
    If aCheckBox.Value = True Then ADSinputform.Controls(chBoxTag).Visible = True
    If aCheckBox.Value = False Then
        ADSinputform.Controls(chBoxTag).Visible = False
        For Each chBox In ADSinputform.Controls(chBoxTag).Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If
End If
End Sub

I'm not sure this can be done, and I'm not real sure where to even start with it. I know I can loop through all the controls and read the state or the combobox selection, but what to do after that?

Naming: Frames: "AlphaSect_Frame", "BetaSect_Frame", "GammaSect_Frame"

First-Level Checkboxes: "A1Checkbox", "A2Checkbox", "A3Checkbox"... "B1Checkbox", "B2Checkbox"... "C1Checkbox", "C2Checkbox"

Second-Level Checkboxes: "A1P1Checkbox", "A1P2Checkbox", "A2P1Checkbox", "A2P2Checkbox"... "B1P1Checkbox", "B1P2Checkbox", "B2P1Checkbox", "B2P2Checkbox"... "C1P1Checkbox", "C1P2Checkbox", "C2P1Checkbox", "C2P2Checkbox"

Userform Screenshots:

You can access controls directly using Controls("ControlNameHere") so you can use your defined naming convention to copy the setting between two controls as long as you know their names. Each frame has its own Controls collection. — Tim Williams
– Tim Williams, Commented Jan 25, 2016 at 17:39
@TimWilliams are you saying I can replicate "Alpha" frame to "Beta" frame, and don't have to grab each, individual selection within "Alpha" (and its sub-frames) to activate each, individual checkbox within "Beta"? — JGR
– JGR, Commented Jan 25, 2016 at 17:57
No - you still need to loop over the controls and map them to the corresponding controls in the other frame, based on your naming convention. See my toy example below. — Tim Williams
– Tim Williams, Commented Jan 25, 2016 at 18:07
@TimWilliams aha - gotcha. I'll give that a shot. With a combobox, what's the corresponding value for ".Value" (is it still ".Value" if I want to copy over "850")? I'll come back shortly after some trial and error - thank you. — JGR
– JGR, Commented Jan 25, 2016 at 18:28
I only tested with checkboxes - should be easy enough to find out for the other control types. — Tim Williams
– Tim Williams, Commented Jan 25, 2016 at 18:34

Tim Williams · Accepted Answer · 2016-01-25 18:05:52Z

1

Here's a simple example for a form with two frames, each of which has two checkboxes:

Dim f1 As Frame, f2 As Frame, c As Control

Set f1 = Me.Frame1 'has checkboxes "f1cb1", "f1cb2"
Set f2 = Me.Frame2 'has checkboxes "f2cb1", "f2cb2"

'loop over all controls in Frame 1
For Each c In f1.Controls
    If TypeName(c) = "CheckBox" Then
        'set the value of the corresponding control in the other fame
        Me.Controls(Replace(c.Name, "f1", "f2")).Value = c.Value
    End If
Next c

answered Jan 25, 2016 at 18:05

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

4 Comments

JGR Over a year ago

Tim - having a hard time with this, it's not checking any checkboxes. I put in a message box to troubleshoot (

MsgBox "currently on " & Me.Controls(c.Name).Name & vbCrLf & _          "copying " & Me.Controls(Replace(c.Name, "f1", "f2")).Name

) and it doesn't look like it's replacing the frame name (I get "Currently on A2Checkbox copying A2Checkbox).

JGR Over a year ago

I adjusted it with "alpha" and "a" without change. (e.g. MsgBox "currently on " & Me.Controls(c.Name).Name & vbCrLf & _ "copying " & Me.Controls(Replace(c.Name, "a", "b")).Name still yields "Currently on A2Checkbox copying A2Checkbox") and Me.Controls(Replace(c.Name, "a", "b")).Value = c.Value (or alpha) does not duplicate a selection.) I feel kind of bad because I don't know enough to know what isn't supposed to work, so I'm changing things with best-guesses.

Tim Williams Over a year ago

My example uses a simpler naming convention than you are using - you need to adjust the "f1" and "f2" to whatever makes sense in your case. In your case (for example) you would replace the initial "A" with a "B" if copying from Alpha frame to Beta frame. In your case, since you just have single-letter Frame identifiers, you should limit the Replace using additional parameters Replace(c.Name, "A", "B", 1, 1) - that will limit to a single replacement instead of replacing all instances of (eg) "A"

JGR Over a year ago

Really, really sorry, Tim - the upper/lower case made a difference. When I wrote, Replace(c.Name, "a", "b") it replaced nothing because the convention is upper case. I used Replace(c.Name, "A","B") and it works fine. Thank you so much for the help on that, and I'm sorry about the user (coder??) error problems.

Collectives™ on Stack Overflow

Save userform checkbox selections to array (to replicate on userform)

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related