Matching a checkbox caption to a cell's value

Question

There are 30 checkboxes that need to have their caption compared to a cell value.

The basic code is repeated 30 times for 30 different check boxes

The only Lines that Change per CheckBox are the Following:

• The First Line - CheckBox3.Value (will increase by 1 for every checkbox)
• Fourth Line from Bottom - Cells(eRow, 24).Value = Controls("CheckBox" & 3).Caption (Needs to match Checkbox Selected (3, 4, 5 etc.))
• Third Line from Bottom - Cells(eRow, 25).Value = TextBox11.Value (will increase by 1 for every checkbox)

The basic block is:

If CheckBox3.Value = True Then
    eRow = Sheet3.Cells(Rows.Count, 6).End(xlUp).Offset(1, 0).Row
    Cells(eRow, 7).Value = ComboBox1.Value
    If CheckBox29.Value = True Then
        Cells(eRow, 8).Value = Controls("CheckBox" & 29).Caption
            If CheckBox1.Value = True Then
                Cells(eRow, 14).Value = "Yes"
            Else
                Cells(eRow, 14).Value = "No"
            End If
        Cells(eRow, 15).Value = ComboBox6.Value
        Cells(eRow, 16).Value = ComboBox7.Value
            If CheckBox2.Value = True Then
                Cells(eRow, 17).Value = "Yes"
            Else
                Cells(eRow, 17).Value = "No"
            End If
        Cells(eRow, 18).Value = ComboBox8.Value
        Cells(eRow, 19).Value = TextBox5.Value
        Cells(eRow, 20).Value = TextBox6.Value
        Cells(eRow, 21).Value = TextBox7.Value
        Cells(eRow, 22).Value = TextBox8.Value
        Cells(eRow, 26).Value = TextBox9.Value
        Cells(eRow, 27).Value = TextBox10.Value
    Else
        Cells(eRow, 8).Value = "Sales Call"
        Cells(eRow, 15).Value = "-"
        Cells(eRow, 16).Value = "-"
        Cells(eRow, 18).Value = "-"
        Cells(eRow, 19).Value = "-"
        Cells(eRow, 20).Value = "-"
        Cells(eRow, 21).Value = "-"
        Cells(eRow, 22).Value = "-"
        Cells(eRow, 26).Value = "-"
        Cells(eRow, 27).Value = "-"
        Cells(eRow, 14).Value = "No"
        Cells(eRow, 17).Value = "No"
    End If
    Cells(eRow, 9).Value = ComboBox3.Value
    Cells(eRow, 10).Value = TextBox2.Value
    Cells(eRow, 11).Value = TextBox3.Value
    Cells(eRow, 12).Value = TextBox4.Value
    Cells(eRow, 13).Value = ComboBox4.Value
    Cells(eRow, 23).Value = ComboBox5.Value
    Cells(eRow, 24).Value = Controls("CheckBox" & 3).Caption
    Cells(eRow, 25).Value = TextBox11.Value
    Cells(eRow, 6).Value = TextBox1.Value
End If

The above code is repeated another 29 times with those three lines changed to match the corresponding checkbox.

Addendum

I have the Linked Spread Sheet - Follow Up Spread Sheet

Basically I created a userform for my sales reps to fill out with there sales calls. we have approximately 30 different product lines so I made it where they could enter one call and if they talked about more than one of our lines they could just check they box and add a comment. so in theory if they talked about all the products we offer the one call would have 30 new lines in the spreadsheet.

The macro works great and does what it needs to but the problem I am running into was with 32 bit Excel versions they would get an error saying file is to large so I broke the code into 2 which isn't very efficient.

I am trying to use loops and arrays but since I am teaching myself how to code I am running into issues.

Answer 1

My solution is based off the OP's workbook which I got from a download link in his revision that got rolled back.

Iterating Over the Controls

The Userform uses 27 sets of ComboBoxes and TextBoxes. The ComboBoxes are numbered [3-28, 30] and are associated with the TextBoxes [3-28, 37] respectively and are named [Control Type + Index]. I used n to iterate over the control and added an exception for n = 30 -> Controls("TextBox" & n + IIf(n = 30, 7, 8)).Value

CallsTable:ListObject

I wrote Function MapTableRow() to take advantage of the data being added to a Table/ListObject.

MapTableRow():Function

Adds the cell references of a row in the table paired with the cell column header name to a dictionary. In this way, the cells of the row can be referred to by name. If the RowIndex is omitted, a new row will be created.

Note: MapTableRow() needs to be added either to the CallsUserForm or a Public Code Module for this to work.

Function MapTableRow(tbl As ListObject, Optional RowIndex As Long = -1) As Object
    Dim ColumnIndex As Long
    Dim dic As Object
    Set dic = CreateObject("Scripting.Dictionary")

    If RowIndex = -1 Then RowIndex = tbl.ListRows.Add.Index

    For ColumnIndex = 1 To tbl.ListColumns.Count
        dic.Add Key:=tbl.ListColumns(ColumnIndex).Name, Item:=tbl.DataBodyRange.Cells(RowIndex, ColumnIndex)
    Next
    Set MapTableRow = dic
End Function

Usage

 Set map = MapTableRow(Sheet3.ListObjects(1), 1)

Refactored Code

Dim map As Object
Dim n As Long

For n = 3 To 30
    If Not n = 29 And Me.Controls("CheckBox" & n).Value Then
        Set map = MapTableRow(Sheet3.ListObjects(1))
        'map("(Do t Modify).Value Product Visit") = ' 2
        'map("(Do t Modify).Value Row Checksum") = ' 3
        'map("(Do t Modify).Value Modified On") = ' 4
        'map("Modified On").Value = ' 5
        map("Date Of Call").Value = TextBox1.Value      ' 6
        map("PGP Rep").Value = ComboBox1.Value          ' 7
        map("Call Type").Value = IIf(CheckBox29.Value, CheckBox29.Caption, "Sales Call")    ' 8
        map("Company Type").Value = ComboBox3.Value     ' 9
        map("Project Discussed").Value = TextBox2.Value    ' 10
        map("Company Name").Value = TextBox3.Value      ' 11
        map("Contact").Value = TextBox4.Value           ' 12
        map("Contact Type").Value = ComboBox4.Value     ' 13
        map("Focus 10").Value = IIf(CheckBox29.Value And CheckBox1.Value, "Yes", "No")    ' 14
        map("Project Type").Value = IIf(CheckBox29.Value, ComboBox6.Value, "-")    ' 15
        map("Project Status").Value = IIf(CheckBox29.Value, ComboBox7.Value, "-")    ' 16
        map("Target Project").Value = IIf(CheckBox29.Value And CheckBox2.Value, "Yes", "No")    ' 17
        map("Project Region").Value = IIf(CheckBox29.Value, ComboBox8.Value, "-")    ' 18
        map("Project City").Value = IIf(CheckBox29.Value, TextBox5.Value, "-")    ' 19
        map("GC #01").Value = IIf(CheckBox29.Value, TextBox6.Value, "-")    ' 20
        map("GC #02").Value = IIf(CheckBox29.Value, TextBox7.Value, "-")    ' 21
        map("GC #03").Value = IIf(CheckBox29.Value, TextBox8.Value, "-")    ' 22
        map("Entertainment").Value = ComboBox5.Value    ' 23
        map("PGP Product").Value = Controls("CheckBox" & n).Caption    ' 24
        map("Comments").Value = Controls("TextBox" & n + IIf(n = 30, 7, 8)).Value    ' 25
        map("Article Link").Value = IIf(CheckBox29.Value, TextBox9.Value, "-")    ' 26
        map("Project Photo Link").Value = IIf(CheckBox29.Value, TextBox10.Value, "-")    ' 27
        'map("Status").Value = ' 28
    End If
Next

Observations

None of the controls on the userform are named. This makes it very difficult to develop and modify the code.

The current design is very rigid. The way it is designed makes it difficult to load the data back into the userform. It is also going to be difficult to maintain as the company grows.

Alternatively. I would insert new rows of controls into a MSForms.Frame control and have each row of controls associated with a row in the table.

Answer 2

Anywhere there is repetition, there is a function screaming to be born. What is not clear from your code is whether all the same cells are changed (i.e. eRow is the same in all 30 cases) but I will assume this is the case.

Sub HandleCheckBox(SomeText as String, OtherText as String) 'no need to return anything
    Dim tempBool as Boolean ' lousy name, but this is just to shorten code
    Dim eRow as Range
    Set eRow = Sheet3.Cells(Rows.Count, 6).End(xlUp).EntireRow.Columns.Offset(1,0) 
    tempBool = CheckBox29.Value
    eRow(6) = TextBox1.Value
    eRow(7) = ComboBox1.Value
    eRow(8) = IIf(tempBool, CheckBox29.Value, "Sales Call")
    eRow(9) = ComboBox3.Value
    eRow(10) = TextBox2.Value
    eRow(11) = TextBox3.Value
    eRow(12) = TextBox4.Value
    eRow(13) = ComboBox4.Value
    eRow(14) = IIf(tempBool And CheckBox1.Value, "Yes", "No")
    eRow(15) = IIf(tempBool, ComboBox6.Value, "-")
    eRow(16) = IIf(tempBool, ComboBox7.Value, "-")
    eRow(17) = IIf(tempBool And CheckBox2.Value, "Yes", "No")
    eRow(18) = IIf(tempBool, ComboBox8.Value, "-")
    eRow(19) = IIf(tempBool, TextBox5.Value, "-")
    eRow(20) = IIf(tempBool, TextBox6.Value, "-")
    eRow(21) = IIf(tempBool, TextBox7.Value, "-")
    eRow(22) = IIf(tempBool, TextBox8.Value, "-")
    eRow(23) = ComboBox5.Value
    eRow(24) = SomeText
    eRow(25) = OtherText
    eRow(26) = IIf(tempBool, TextBox9.Value, "-")
    eRow(27) = IIf(tempBool, TextBox10.Value, "-")
End Sub   

Now all that is left is to call it.

You could do this in a series of calls

'[... Your main sub ...]
If Checkbox3.Value then HandleCheckBox CheckBox3.Caption, TextBox11.Caption
If Checkbox4.Value then HandleCheckBox CheckBox4.Caption, TextBox12.Caption
'[... Rest of code ...]

Or perhaps in a series of handlers

Private Sub CheckBox3_Change()
    If Checkbox3.Value then HandleCheckBox CheckBox3.Caption, TextBox11.Caption
End Sub

Or perhaps in a loop

For j = 1 to 30
   cbNumber = j + 2
   tbNumber = j + 10
   If Controls("Checkbox" & cbNumber).Value then HandleCheckBox Controls("Checkbox" & cbNumber).Caption, Controls("TextBox" & tbNumber).Caption
Next j