Solved: Unable to add another record thru click event

[shahcu]

Hi!
I have created a VB form (Excel VBA) where in when user put a check mark against any check box "TRUE" will get store in excel sheet. I have mentioned the coding in click event. However i am unable to add more than one record.
Code is as follows:

[VBA]
Private Sub CommandButton1_Click()

r = 2

ActiveSheet.Cells(r, 1) = TextBox1.Value
If CheckBox1.Value = True Then
ActiveSheet.Cells(r, 2) = True
Else
ActiveSheet.Cells(r, 2) = False
End If
If CheckBox2.Value = True Then
ActiveSheet.Cells(r, 3) = True
Else
ActiveSheet.Cells(r, 3) = False
End If
If CheckBox3.Value = True Then
ActiveSheet.Cells(r, 4) = True
Else
ActiveSheet.Cells(r, 4) = False
End If
If CheckBox4.Value = True Then
ActiveSheet.Cells(r, 5) = True
Else
ActiveSheet.Cells(r, 5) = False
End If
If CheckBox5.Value = True Then
ActiveSheet.Cells(r, 6) = True
Else
ActiveSheet.Cells(r, 6) = False
End If
If CheckBox6.Value = True Then
ActiveSheet.Cells(r, 7) = True
Else
ActiveSheet.Cells(r, 7) = False
End If
If CheckBox7.Value = True Then
ActiveSheet.Cells(r, 8) = True
Else
ActiveSheet.Cells(r, 8) = False
End If
If CheckBox8.Value = True Then
ActiveSheet.Cells(r, 9) = True
Else
ActiveSheet.Cells(r, 9) = False
End If
'Next r
End Sub
[/VBA]

The above code will save one particular transaction ... But i need to save more than one records... Pls help...

[Charlize]

[VBA]Private Sub CommandButton1_Click()
Dim r As Long
'first row is heading so + 1 - starting on 2
'when 2 is filled in, new row will be 3.
r = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row + 1
ActiveSheet.Cells(r, 1) = TextBox1.Value
If CheckBox1.Value = True Then
ActiveSheet.Cells(r, 2) = True
Else
ActiveSheet.Cells(r, 2) = False
End If
'... rest of your code
End Sub[/VBA]

[moa]

[vba]Private Sub CommandButton1_Click()
Dim X As Long, r As Long

r = ActiveSheet.range("A" & Rows.Count).End(xlUp).Row + 1
ActiveSheet.Cells(r, 1) = TextBox1.Value

For X = 1 To 8
ActiveSheet.Cells(r, X + 1) = Me.Controls("CheckBox" & X).Value
Next X
End Sub
[/vba]