Multiple userform checkbox values-CodePudding

I am trying to take the values passed from a userform that has the following checkbox options and write them to a single concatenated cell.

Scenario Options and Userform Output Cells

userform

Thank you for your help. Any suggestions would be greatly appreciated. I currently have attempted this, but can only get one option to show up at a time.

Private Sub CheckBox1_Click()

If Me.CheckBox1.Value = True Then
    Me.Frame2.Visible = True
    Sheets("Project Analysis - Summary").Range("D7") = 1
Else
    Me.Frame2.Visible = False
End If

End Sub

Private Sub CheckBox2_Click()

If Me.CheckBox2.Value = True Then
    Me.Frame3.Visible = True
    Sheets("Project Analysis - Summary").Range("D8") = 1
Else
    Me.Frame3.Visible = False
End If

End Sub

Private Sub CheckBox3_Click()

If Me.CheckBox3.Value = True Then
    Me.Frame4.Visible = True
    Sheets("Project Analysis - Summary").Range("D9") = 1
Else
    Me.Frame4.Visible = False
End If

End Sub

Private Sub CheckBox5_Click()

If Me.CheckBox5.Value = True Then
    Me.Frame6.Visible = True
    Sheets("Project Analysis - Summary").Range("D10") = 1
Else
    Me.Frame6.Visible = False
End If

End Sub

Private Sub CheckBox6_Click()

If Me.CheckBox6.Value = True Then
    Me.Frame5.Visible = True
    Sheets("Project Analysis - Summary").Range("D11") = 1
Else
    Me.Frame5.Visible = False
End If

End Sub

Private Sub CommandButton1_Click()

Dim ctrl1 As Control

Set ctrl1 = Nothing
    
For Each ctrl1 In Me.Frame2.Controls
    If TypeName(ctrl1) = "OptionButton" And ctrl1.Value = True Then
        Sheets("Project Analysis - Summary").Range("E7") = ctrl1.Caption
    End If
Next

Dim ctrl2 As Control

Set ctrl2 = Nothing
    
For Each ctrl2 In Me.Frame3.Controls
    If TypeName(ctrl2) = "OptionButton" And ctrl2.Value = True Then
        Sheets("Project Analysis - Summary").Range("E8") = ctrl2.Caption
    End If
Next

Dim ctrl3 As Control

Set ctrl3 = Nothing
    
For Each ctrl3 In Me.Frame4.Controls
    If TypeName(ctrl3) = "OptionButton" And ctrl3.Value = True Then
        Sheets("Project Analysis - Summary").Range("E9") = ctrl3.Caption
    End If
Next

Dim ctrl4 As Control

Set ctrl4 = Nothing
    
For Each ctrl4 In Me.Frame5.Controls
    If TypeName(ctrl4) = "OptionButton" And ctrl4.Value = True Then
        Sheets("Project Analysis - Summary").Range("E10") = ctrl4.Caption
    End If
Next

Dim ctrl5 As Control

Set ctrl5 = Nothing
    
For Each ctrl5 In Me.Frame6.Controls
    If TypeName(ctrl5) = "OptionButton" And ctrl5.Value = True Then
        Sheets("Project Analysis - Summary").Range("E11") = ctrl5.Caption
    End If
Next

End Sub


Private Sub UserForm_Initialize()

Me.Frame2.Visible = False
Me.Frame3.Visible = False
Me.Frame4.Visible = False
Me.Frame5.Visible = False
Me.Frame6.Visible = False
Sheets("Project Analysis - Summary").Range("D7") = ""
Sheets("Project Analysis - Summary").Range("D8") = ""
Sheets("Project Analysis - Summary").Range("D9") = ""
Sheets("Project Analysis - Summary").Range("D10") = ""
Sheets("Project Analysis - Summary").Range("D11") = ""
Sheets("Project Analysis - Summary").Range("E7") = ""
Sheets("Project Analysis - Summary").Range("E8") = ""
Sheets("Project Analysis - Summary").Range("E9") = ""
Sheets("Project Analysis - Summary").Range("E10") = ""
Sheets("Project Analysis - Summary").Range("E11") = ""

End Sub

updated code:

Const sOptions As String = "Self-Financing,Equity financing by Coppermine,Debt financing by Coppermine," & _
        "Equity Financing by External Party,Debt Financing by external party,Customer financing"

Private Sub CheckBox1_Click()

If Me.CheckBox1.Value = True Then
    Me.Frame2.Visible = True
    Sheets("Project Analysis - Summary").Range("D7") = 1
Else
    Me.Frame2.Visible = False
End If

End Sub

Private Sub CheckBox2_Click()

If Me.CheckBox2.Value = True Then
    Me.Frame3.Visible = True
    Sheets("Project Analysis - Summary").Range("D8") = 1
Else
    Me.Frame3.Visible = False
End If

End Sub

Private Sub CheckBox3_Click()

If Me.CheckBox3.Value = True Then
    Me.Frame4.Visible = True
    Sheets("Project Analysis - Summary").Range("D9") = 1
Else
    Me.Frame4.Visible = False
End If

End Sub


Private Sub CheckBox5_Click()

If Me.CheckBox5.Value = True Then
    Me.Frame6.Visible = True
    Sheets("Project Analysis - Summary").Range("D10") = 1
Else
    Me.Frame6.Visible = False
End If

End Sub

Private Sub CheckBox6_Click()

If Me.CheckBox6.Value = True Then
    Me.Frame5.Visible = True
    Sheets("Project Analysis - Summary").Range("D11") = 1
Else
    Me.Frame5.Visible = False
End If

End Sub
Private Sub CommandButton1_Click()
        'process each list box looking for values
        Dim selectedOptions As String
        With ListBox1
                For idx = 0 To .ListCount - 1
                        If .Selected(idx) Then
                                selectedOptions = selectedOptions & .List(idx) & ","
                        End If
                Next idx
        End With
        MsgBox selectedOptions
        'save the value to your worksheet
        selectedOptions = ""
        ' repeat for all of the list boxes.
        
        'process each list box looking for values
        With ListBox2
                For idx = 0 To .ListCount - 1
                        If .Selected(idx) Then
                                selectedOptions = selectedOptions & .List(idx) & ","
                        End If
                Next idx
        End With
        MsgBox selectedOptions
        'save the value to your worksheet
        selectedOptions = ""
        ' repeat for all of the list boxes.
        
        'process each list box looking for values
        With ListBox3
                For idx = 0 To .ListCount - 1
                        If .Selected(idx) Then
                                selectedOptions = selectedOptions & .List(idx) & ","
                        End If
                Next idx
        End With
        MsgBox selectedOptions
        'save the value to your worksheet
        selectedOptions = ""
        ' repeat for all of the list boxes.
        
        'process each list box looking for values
        With ListBox4
                For idx = 0 To .ListCount - 1
                        If .Selected(idx) Then
                                selectedOptions = selectedOptions & .List(idx) & ","
                        End If
                Next idx
        End With
        MsgBox selectedOptions
        'save the value to your worksheet
        selectedOptions = ""
        ' repeat for all of the list boxes.
        
        'process each list box looking for values
        With ListBox5
                For idx = 0 To .ListCount - 1
                        If .Selected(idx) Then
                                selectedOptions = selectedOptions & .List(idx) & ","
                        End If
                Next idx
        End With
        MsgBox selectedOptions
        'save the value to your worksheet
        selectedOptions = ""
        ' repeat for all of the list boxes.
        
    
End Sub


Private Sub UserForm_Initialize()

Me.Frame2.Visible = False
Me.Frame3.Visible = False
Me.Frame4.Visible = False
Me.Frame5.Visible = False
Me.Frame6.Visible = False
Sheets("Project Analysis - Summary").Range("D7") = ""
Sheets("Project Analysis - Summary").Range("D8") = ""
Sheets("Project Analysis - Summary").Range("D9") = ""
Sheets("Project Analysis - Summary").Range("D10") = ""
Sheets("Project Analysis - Summary").Range("D11") = ""
Sheets("Project Analysis - Summary").Range("E7") = ""
Sheets("Project Analysis - Summary").Range("E8") = ""
Sheets("Project Analysis - Summary").Range("E9") = ""
Sheets("Project Analysis - Summary").Range("E10") = ""
Sheets("Project Analysis - Summary").Range("E11") = ""

        ListBox1.ListStyle = fmListStyleOption
        ListBox1.MultiSelect = fmMultiSelectMulti
        ListBox1.List = Split(sOptions, ",")
        ListBox2.ListStyle = fmListStyleOption
        ListBox2.MultiSelect = fmMultiSelectMulti
        ListBox2.List = Split(sOptions, ",")
        ListBox3.ListStyle = fmListStyleOption
        ListBox3.MultiSelect = fmMultiSelectMulti
        ListBox3.List = Split(sOptions, ",")
        ListBox4.ListStyle = fmListStyleOption
        ListBox4.MultiSelect = fmMultiSelectMulti
        ListBox4.List = Split(sOptions, ",")
        ListBox5.ListStyle = fmListStyleOption
        ListBox5.MultiSelect = fmMultiSelectMulti
        ListBox5.List = Split(sOptions, ",")

End Sub

CodePudding user response：

Change your options in each of the 5 frames from checkboxes to listboxes. Keep your current UserForm_initialize but add what is in the example below to it. Keep your current selection for showing the 5 frames. Change your CommandButton to process the listbox as in the example below.

Example: Create a user form and paste this code into it. Add a command button and a list box to the frame and run it to see how it works.

Const sOptions As String = "Self-Financing,Equity financing by Coppermine,Debt financing by Coppermine," & _
        "Equity Financing by External Party,Debt Financing by external party,Customer financing"

Private Sub CommandButton1_Click()
        'process each list box looking for values
        Dim selectedOptions As String
        With ListBox1
                For idx = 0 To .ListCount - 1
                        If .Selected(idx) Then
                                selectedOptions = selectedOptions & .List(idx) & ","
                        End If
                Next idx
        End With
        MsgBox selectedOptions
        'save the value to your worksheet
        selectedOptions = ""
        ' repeat for all of the list boxes.
End Sub

Private Sub UserForm_Initialize()
        ' repeat this setup for each of the frame's content, with a unique List Box in each
        ListBox1.ListStyle = fmListStyleOption
        ListBox1.MultiSelect = fmMultiSelectMulti
        ListBox1.List = Split(sOptions, ",") ' set the values for the list box
End Sub