Search code examples
vbacheckboxuserform

Skip Unchecked Checkbox and copy the Caption onto sheet


I have this userform with checkboxes and I wanted to copy the caption of only the checked boxes and arrange them in sequence.

What my code does right now: if the checkbox is unchecked, return empty cell value then move onto the next target cell. I don't know how to skip the unchecked box.

Here is my code (inside 'Submit' Button on click):

'----- Transfer Add-On Items to Printout Sheet ---------
Dim i           As Integer
Dim j           As Integer

'---- Category 1 ------

i = 6
For j = 1 To 9
    If UserForm1.Controls("Checkbox" & j).Value = True Then
        ThisWorkbook.Sheets("Printout").Range("A" & i).Value = Me.Controls("Checkbox" & j).Caption
    Else
        ThisWorkbook.Sheets("Printout").Range("A" & i).Value = ""
    End If
i = i + 2
Next j

I have 9 checkboxes and are numbered to increase vertically downwards, and the target cells are starting from A6 with 2 rows increment (A6, A8, A10, up to A22).

enter image description here


Solution

  • Put i variable in section, like in code below. Empty values just rewrite by new values. To be honest, I don't understand why you need the Else block

    i = 6
    For j = 1 To 9
        If UserForm1.Controls("Checkbox" & j).Value = True Then
            ThisWorkbook.Sheets("Printout").Range("A" & i).Value = Me.Controls("Checkbox" & j).Caption
            i = i + 2 'here
        Else
            ThisWorkbook.Sheets("Printout").Range("A" & i).Value = ""
        End If
    Next j