Excel 2010 VBA Userform Add and Clear Button Issue

[jhedges]

Team,

I have the following code to take information from a userform and place into an Excel ListObject. The listobject is A6:W6 with headers in A5:W5 and I would like it to grow with each useerform add button click. A6 has a formula to autonumber the rows. Columns L, S, T and U have formulas. The code will add data from the userform starting in B6 and into the other column cells allowing the formulas in the worksheet to display values. The code places data from the userfrom into the 6th row at B6, when I initialize the form again and click add it places data into the 7th row at B7 and finally when I repeat add data from the userform the code then overwrites the data in the 7th row at B7 instead of putting in a new 8th row starting at B8. Maybe I need to do a line with an xldown code or something...?

Also, I would like the userform txtNotes textbox data to be transfered to the W6 column as a comment. I have found the code ActiveCell.AddComment.Text TextBox1.Text & Chr(10) & TextBox2.Text, which I have in the code below; however, I haven't adjusted it to fit...as I'm not sure how?

Sub Add()
Dim tblCash As Integer
tblCash = WorksheetFunction.CountA(Sheets("CSI Cash Register Cash Count").Range("B6")) + 6
'ActiveSheet.Unprotect Password:="XXXXX"
'On Error Resume Next
'Validate Comboxes
If frmCashCount.ComboBox1.Value = "" Then
    MsgBox "'Site' is a mandatory field...", vbOKOnly, "Required Field"
    frmCashCount.ComboBox1.SetFocus
Exit Sub
ElseIf frmCashCount.ComboBox2.Value = "" Then
    MsgBox "'Staff' is a mandatory field...", vbOKOnly, "Required Field"
    frmCashCount.ComboBox2.SetFocus
Exit Sub
ElseIf frmCashCount.ComboBox3.Value = "" Then
    MsgBox "'Shift' is a mandatory field...", vbOKOnly, "Required Field"
    frmCashCount.ComboBox3.SetFocus
Exit Sub
End If

Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 2).Value = frmCashCount.Label53.Caption
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 3).Value = frmCashCount.ComboBox1.Value
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 4).Value = frmCashCount.ComboBox2.Value
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 5).Value = frmCashCount.ComboBox3.Value
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 6).Value = frmCashCount.Label35.Caption
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 7).Value = frmCashCount.Label36.Caption
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 8).Value = frmCashCount.Label37.Caption
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 9).Value = frmCashCount.Label38.Caption
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 10).Value = frmCashCount.Label39.Caption
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 11).Value = frmCashCount.Label40.Caption
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 13).Value = frmCashCount.Label42.Caption
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 14).Value = frmCashCount.Label43.Caption
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 15).Value = frmCashCount.Label44.Caption
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 16).Value = frmCashCount.Label45.Caption
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 17).Value = frmCashCount.Label46.Caption
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 18).Value = frmCashCount.Label47.Caption
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 22).Value = frmCashCount.txtNotes.Value
ActiveCell.AddComment.Text TextBox1.Text & Chr(10) & TextBox2.Text
'ActiveSheet.Protect Password:="XXXXX", AllowFiltering:=True

Unload frmCashCount
End Sub

Finally, I have the following code to clear the userform frmCashCount, which it does clear all the textboxes. I would also like for it to clear all the the labels that I'm using to display captions; however, leave certain labels that display captions for the textboxes and comboboxes. Does anyone have any ideas?

Sub ClearForm()
Dim z As Control
For Each z In frmCashCount.Controls
If TypeName(z) = "TextBox" Then
z.Value = ""
Else
    If TypeName(z) = "ComboBox" Then
    z.Value = ""
    End If
End If
Next z
frmCashCount.Repaint
End Sub

 

[jhedges]

Just wondering if anyone could be of help on this topic?