Search code examples
excelvba

When checkboxes are ticked, captions should be put in a comma separated string and be placed into an array

I want when checkboxes are checked, its captions be put in a comma separated string and be placed into an array.

Checkboxes 1 to 12 will be for the Profits column.
Checkboxes 13 to 24 will be for the Capital column.

The code where I would like to insert the code for the checkboxes.

Private Sub SUBMIT_Click()

    Dim sh As Worksheet, msg As String, arr, c As Range, id
    
    'check for any empty required fields
    If Len(TextBox1.Value) = 0 Then msg = msg & vbLf & " - Employee ID"

    If Len(msg) > 0 Then 'anything missing?
        MsgBox "The following fields are required:" & msg, _
                vbOKOnly + vbCritical, "Missing Information"
    Else
        'OK to write to sheet
        Set sh = ThisWorkbook.Sheets("Bulk Loader File")
        Set c = sh.Cells(Rows.Count, "A").End(xlUp).Offset(1) '##start adding here
        arr = Split(TextBox1.Value, ",") '##split on comma to get an array
        
            For i = 21 To 35 Step 2                           '## loop over comboboxes
            dataItem = Me.Controls("ComboBox" & i).Value '## read combo values...
            schedule = Me.Controls("ComboBox" & (i + 1)).Value
            If Len(dataItem) > 0 Then             '## any value selected?
            
        For Each id In arr               '##loop over the array
             c.Resize(1, 30).Value = Array(Trim(id), TextBox2.Value, TextBox3.Value, TextBox4.Value, TextBox5.Value, TextBox6.Value, TextBox7.Value, TextBox8.Value, _
                                     ComboBox7.Value, ComboBox8.Value, ComboBox9.Value, ComboBox10.Value, ComboBox11.Value, _
                                     ComboBox12.Value, ComboBox13.Value, ComboBox14.Value, ComboBox15.Value, ComboBox16.Value, _
                                     ComboBox17.Value, ComboBox1.Value, ComboBox2.Value, ComboBox3.Value, ComboBox4.Value, _
                                     ComboBox5.Value, ComboBox6.Value, ComboBox18.Value, ComboBox19.Value, ComboBox20.Value, dataItem, schedule, Profits, Capital)
             
            Set c = c.Offset(1)          '##next output row
                Next id
            End If
        Next i

        MsgBox "Information Loaded", vbOKOnly + vbInformation, "Notification"
        Unload Me
    End If
    
End Sub

This is my draft code for checkboxes, but I am not sure if this is correct and how it will be placed in my current code.

Private Sub CommandButton1_Click()

    Dim sh As Worksheet, msg As String, arr, c As Range, id
    
    'check for any empty required fields
    If Len(TextBox1.Value) = 0 Then msg = msg & vbLf & " - Employee ID"

    If Len(msg) > 0 Then 'anything missing?
        MsgBox "The following fields are required:" & msg, _
                vbOKOnly + vbCritical, "Missing Information"
    Else
        'OK to write to sheet
        Set sh = ThisWorkbook.Sheets("Bulk Loader File")
        Set c = sh.Cells(Rows.Count, "A").End(xlUp).Offset(1) '##start adding here
        arr = Split(CheckBox1.Caption, ",")
        
        For Each xcontrol In Me.Controls
    
        If TypeName(xcontrol) = "CheckBox" And xcontrol.Value = True Then
        
        CheckBox1.Value = "Jan"
        CheckBox2.Value = "Feb"
        CheckBox3.Value = "Mar"
        CheckBox4.Value = "Apr"
        CheckBox5.Value = "May"
        CheckBox6.Value = "Jun"
        CheckBox7.Value = "Jul"
        CheckBox8.Value = "Aug"
        CheckBox9.Value = "Sep"
        CheckBox10.Value = "Oct"
        CheckBox11.Value = "Nov"
        CheckBox12.Value = "Dec"
        
        
        For i = 1 To 12 Step 2                           '## loop over comboboxes
        Profits = Me.Controls("Checkbox" & i).Value '## read combo values...
        If Len(Dividend) > 0 Then             '## any value selected?

        For Each id In arr               '##loop over the array
            c.Resize(1, 2).Value = Array(Trim(id), Dividend)
            Set c = c.Offset(1)          '##next output row
                Next id
            End If
        Next i

        MsgBox "Time Series Attributes Loaded", vbOKOnly + vbInformation, "Notification"
        Unload Me
    End If

End Sub

Sample userform.
enter image description here

When the user ticks one of the boxes, its caption should be put into a comma separated string.
enter image description here

Solution

  • Based on the file you shared and discussion in Chat:

    Private Sub SUBMIT_Click()

    Dim sh As Worksheet, msg As String, arrId, c As Range, id, arr
    Dim profits As String, capitals As String, i As Long, col As Collection
    
    'check for any empty required fields
    If Len(TextBox1.Value) = 0 Then msg = msg & vbLf & " - Lipper ID"

    If Len(msg) > 0 Then 'anything missing?
        MsgBox "The following fields are required:" & msg, _
                vbOKOnly + vbCritical, "Missing Information"
        Exit Sub 'cleaner to exit here - reduces nesting level of code below
    End If
    
    'OK to write to sheet
    Set sh = ThisWorkbook.Sheets("Bulk Loader File")
    Set c = sh.Cells(Rows.Count, "A").End(xlUp).Offset(1) '##start adding here
    arrId = Split(TextBox1.Value, ",") '##split on comma to get an array
    
    '## collect any checked profit and capital months
    For i = 1 To 12 'if checkboxes are checked then add month name to appropriate string
        If Me.Controls("Checkbox" & i).Value = True Then        ' 1 to 12 <TW> True not "True"....
            AddWithComma profits, MonthName(i)
        End If
        If Me.Controls("Checkbox" & (12 + i)).Value = True Then ' 13 to 24
            AddWithComma capitals, MonthName(i)
        End If
    Next i
    
    '## check Asset Collection Schedule for any selections
    Set col = New Collection
    For i = 21 To 35 Step 2                           '## loop over comboboxes
        dataItem = Me.Controls("ComboBox" & i).Value '## read combo values...
        schedule = Me.Controls("ComboBox" & (i + 1)).Value
        If Len(dataItem) > 0 Then col.Add Array(dataItem, schedule) '## add any selected pairs to collection
    Next i
    If col.Count = 0 Then col.Add Array("", "") 'nothing was selected, so add one set of blank values
    
    'write out records
    For Each id In arrId               '##loop over the array
        For Each arr In col            '...and the collection
            c.Resize(1, 32).Value = _
               Array(Trim(id), TextBox2.Value, TextBox3.Value, TextBox4.Value, TextBox5.Value, TextBox6.Value, _
                  TextBox7.Value, TextBox8.Value, ComboBox7.Value, ComboBox8.Value, ComboBox9.Value, ComboBox10.Value, _
                  ComboBox11.Value, ComboBox12.Value, ComboBox13.Value, ComboBox14.Value, ComboBox15.Value, ComboBox16.Value, _
                  ComboBox17.Value, ComboBox1.Value, ComboBox2.Value, ComboBox3.Value, ComboBox4.Value, _
                  ComboBox5.Value, ComboBox6.Value, ComboBox18.Value, ComboBox19.Value, ComboBox20.Value, _
                  arr(0), arr(1), profits, capitals)
         
            Set c = c.Offset(1)          '##next output row
        Next arr
    Next id
    
    MsgBox "Time Series Attributes Loaded", vbOKOnly + vbInformation, "Notification"
    Unload Me
    
End Sub

''Return month name for month# `monthNum`
Function MonthName(monthNum As Long) As String
    MonthName = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", _
                    "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")(monthNum - 1) 'zero-based array
End Function

'utility sub for adding to a string as a comma-separated value
Sub AddWithComma(ByRef msg As String, addThis As String)
    msg = msg & IIf(Len(msg) > 0, ",", "") & addThis
End Sub