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:
so the text box blank is not skipped in the loop code. thanks for your answer Francesco
Aucun commentaire:
Enregistrer un commentaire