mercredi 3 juin 2020

VBA code: Loop throught an userform on checkboxes and txtboxes and transfer only the checkboxes checked and the textboxes with numbers to worksheet

what I am trying to do is to copy data from a userform to a worksheet. the userform is like this in a simplified version. the output should be in the worksheet like below: correct worksheet output

here is the code i wrote:

Private Sub cmdsave_Click()
Dim chkbox As Integer
Dim ctl As MSForms.Control, i As Integer, lr As Long
Dim op As MSForms.CheckBox

With Me

chkbox = .A.Value + .B.Value + .C.Value
chkbox = Abs(chkbox)

If chkbox <> 0 And .cmbcat <> "" Then

ReDim mval(1 To chkbox, 1 To 3)
    i = 1

    For Each ctl In Me.Controls
        If TypeOf ctl Is MSForms.CheckBox Then
            Set op = ctl
            If op = True Then
                    mval(i, 1) = .cmbcat.Value
                    mval(i, 2) = op.Caption
                    mval(i, 3) = .Controls("txt" & Right(.)), 1).Value
                i = i + 1
            End If
       End If
    Next

  End If
 End With

 With Sheets("database")
   lr = .Range("A" & .Rows.Count).End(xlUp).Row + 1
   .Range("A" & lr).Resize(UBound(mval, 1), 3) = mval
 End With

 End Sub

The problem is that with this code I get:

incorrect output

so the text box blank is not skipped in the loop code. thanks for your answer Francesco




Aucun commentaire:

Enregistrer un commentaire