Error: Multiple entry on sheet from userform

[joacro]

Hi there,

I have a userform

1. On my userform I've got 9 txt fields, 18 opt boxes and 3 buttons (please see attached form)

My problem is: If I intput a principal name, spouse name and child/ dependants I seem to work just fine,
However, if I input just a principal name and child/dependant instead of just placing one child/adult dependant on the sheet it places 2
As a matter of fact, if no spouse is selected and child/dependant is entered then the last dependant is always repeated.

I have used the following code:

Private Sub CmdAdd_Click()
LR = Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("A" & LR) = txtPrin
Range("I" & LR) = txtTotal
Range("A" & LR + 1) = txtSpouse
If opt1.Value = True Then
Range("A" & LR + 2) = txtCh1.Value & " (kid u 2)"
ElseIf opt2.Value = True Then
Range("A" & LR + 2) = txtCh1.Value & " (kid u 18)"
ElseIf opt3.Value = True Then
Range("A" & LR + 2) = txtCh1.Value & " (over 18)"
End If
If opt4.Value = True Then
Range("A" & LR + 3) = txtCh2.Value & " (kid u 2)"
ElseIf opt5.Value = True Then
Range("A" & LR + 3) = txtCh2.Value & " (kid u 18)"
ElseIf opt6.Value = True Then
Range("A" & LR + 3) = txtCh2.Value & " (over 18)"
End If
If opt7.Value = True Then
Range("A" & LR + 4) = txtCh3.Value & " (kid u 2)"
ElseIf opt8.Value = True Then
Range("A" & LR + 4) = txtCh3.Value & " (kid u 18)"
ElseIf opt9.Value = True Then
Range("A" & LR + 4) = txtCh3.Value & " (over 18)"
End If
If opt10.Value = True Then
Range("A" & LR + 5) = txtCh4.Value & " (kid u 2)"
ElseIf opt11.Value = True Then
Range("A" & LR + 5) = txtCh4.Value & " (kid u 18)"
ElseIf opt12.Value = True Then
Range("A" & LR + 5) = txtCh4.Value & " (over 18)"
End If
If opt13.Value = True Then
Range("A" & LR + 6) = txtCh5.Value & " (kid u 2)"
ElseIf opt14.Value = True Then
Range("A" & LR + 6) = txtCh5.Value & " (kid u 18)"
ElseIf opt15.Value = True Then
Range("A" & LR + 6) = txtCh5.Value & " (over 18)"
End If
If opt16.Value = True Then
Range("A" & LR + 7) = txtCh6.Value & " (kid u 2)"
ElseIf opt17.Value = True Then
Range("A" & LR + 7) = txtCh6.Value & " (kid u 18)"
ElseIf opt18.Value = True Then
Range("A" & LR + 7) = txtCh6.Value & " (over 18)"
End If
If txtSpouse.Value = "" Then
If opt1.Value = True Then
Range("A" & LR + 1) = txtCh1.Value & " (kid u 2)"
ElseIf opt2.Value = True Then
Range("A" & LR + 1) = txtCh1.Value & " (kid u 18)"
ElseIf opt3.Value = True Then
Range("A" & LR + 1) = txtCh1.Value & " (over 18)"
End If
If txtSpouse.Value = "" Then
If opt4.Value = True Then
Range("A" & LR + 2) = txtCh2.Value & " (kid u 2)"
ElseIf opt5.Value = True Then
Range("A" & LR + 2) = txtCh2.Value & " (kid u 18)"
ElseIf opt6.Value = True Then
Range("A" & LR + 2) = txtCh2.Value & " (over 18)"
End If
End If
If opt7.Value = True Then
Range("A" & LR + 3) = txtCh3.Value & " (kid u 2)"
ElseIf opt8.Value = True Then
Range("A" & LR + 3) = txtCh3.Value & " (kid u 18)"
ElseIf opt9.Value = True Then
Range("A" & LR + 3) = txtCh3.Value & " (over 18)"
End If
If opt10.Value = True Then
Range("A" & LR + 4) = txtCh4.Value & " (kid u 2)"
ElseIf opt11.Value = True Then
Range("A" & LR + 4) = txtCh4.Value & " (kid u 18)"
ElseIf opt12.Value = True Then
Range("A" & LR + 4) = txtCh4.Value & " (over 18)"
End If
If opt13.Value = True Then
Range("A" & LR + 5) = txtCh5.Value & " (kid u 2)"
ElseIf opt14.Value = True Then
Range("A" & LR + 5) = txtCh5.Value & " (kid u 18)"
ElseIf opt15.Value = True Then
Range("A" & LR + 5) = txtCh5.Value & " (over 18)"
End If
If opt16.Value = True Then
Range("A" & LR + 6) = txtCh6.Value & " (kid u 2)"
ElseIf opt17.Value = True Then
Range("A" & LR + 6) = txtCh6.Value & " (kid u 18)"
ElseIf opt18.Value = True Then
Range("A" & LR + 6) = txtCh6.Value & " (over 18)"
End If
If txtCh1 = "" Then
Range("A" & LR) = txtPrin
Range("I" & LR) = txtTotal
Range("A" & LR + 1) = txtSpouse
End If
If txtCh1 = "" And txtSpouse = "" Then
Range("A" & LR) = txtPrin
Range("I" & LR) = txtTotal
End If
End If
End Sub

Hope someone can help with this error

Thanx in advance

 

[p45cal]

No attachment (not allowed here)
A guess:

Private Sub CmdAdd_Click()
LR = Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("A" & LR) = txtPrin
Range("I" & LR) = txtTotal
Range("A" & LR + 1) = txtSpouse


If txtSpouse.Value = "" Then myoffset = 0 Else myoffset = 1
If opt1.Value = True Then
    Range("A" & LR + 1 + myoffset) = txtCh1.Value & " (kid u 2)"
ElseIf opt2.Value = True Then
    Range("A" & LR + 1 + myoffset) = txtCh1.Value & " (kid u 18)"
ElseIf opt3.Value = True Then
    Range("A" & LR + 1 + myoffset) = txtCh1.Value & " (over 18)"
End If
If opt4.Value = True Then
    Range("A" & LR + 2 + myoffset) = txtCh2.Value & " (kid u 2)"
ElseIf opt5.Value = True Then
    Range("A" & LR + 2 + myoffset) = txtCh2.Value & " (kid u 18)"
ElseIf opt6.Value = True Then
    Range("A" & LR + 2 + myoffset) = txtCh2.Value & " (over 18)"
End If
If opt7.Value = True Then
    Range("A" & LR + 3 + myoffset) = txtCh3.Value & " (kid u 2)"
ElseIf opt8.Value = True Then
    Range("A" & LR + 3 + myoffset) = txtCh3.Value & " (kid u 18)"
ElseIf opt9.Value = True Then
    Range("A" & LR + 3 + myoffset) = txtCh3.Value & " (over 18)"
End If
If opt10.Value = True Then
    Range("A" & LR + 4 + myoffset) = txtCh4.Value & " (kid u 2)"
ElseIf opt11.Value = True Then
    Range("A" & LR + 4 + myoffset) = txtCh4.Value & " (kid u 18)"
ElseIf opt12.Value = True Then
    Range("A" & LR + 4 + myoffset) = txtCh4.Value & " (over 18)"
End If
If opt13.Value = True Then
    Range("A" & LR + 5 + myoffset) = txtCh5.Value & " (kid u 2)"
ElseIf opt14.Value = True Then
    Range("A" & LR + 5 + myoffset) = txtCh5.Value & " (kid u 18)"
ElseIf opt15.Value = True Then
    Range("A" & LR + 5 + myoffset) = txtCh5.Value & " (over 18)"
End If
If opt16.Value = True Then
    Range("A" & LR + 6 + myoffset) = txtCh6.Value & " (kid u 2)"
ElseIf opt17.Value = True Then
    Range("A" & LR + 6 + myoffset) = txtCh6.Value & " (kid u 18)"
ElseIf opt18.Value = True Then
    Range("A" & LR + 6 + myoffset) = txtCh6.Value & " (over 18)"
End If
End Sub

 

[joacro]

Thank you so much, it works perfectly. What did you do

 

[p45cal]

Surely you can examine the differences in the code without my iterating every change?
You had two blocks of code which were essentially the same, bar which row data was placed on. So I removed one of the blocks then I just added another variable (myoffset), set to 0 or 1 depending on whether there was a spouse or not and liberally sprinkled + myoffset around the code. You also had one or two unnecessary If statements, and several repeated lines which I removed.

 

[joacro]

Once again, thank you!!! It surely helped. I don't know a lot about vba and is still learning. Not using it every day

Please check out my other post

Regards

Joacro