Insert data (from a form) into the next empty row on a worksheet

This is kind of a cheap trick, but it's simple and works very well.

First, locate an empty cell on your worksheet, that's out of view. I always keep this to one in Row 1, usually AA1. In that cell, type:

COUNTA(A:A)

This will contain a count of the items in column A (assuming that's where the data starts, change it to B:B if you wish).

Now insert this for your userform code:

[tt]
Option Explicit
[color green] 'The Row variable isn't just a name, not the
'actual row of cells[/color]
Dim Data as Object
Dim Row as Integer
Dim I as Integer

Private Sub UserForm_Initialize()
Set Data = Sheets("Sheet1").Cells
End Sub

Private Sub BtnAdd_click()[color green]
'Row references that cell in the worksheet. The (1, 27) is
'row 1, column 27, which is where I said to put it.[/color]
Row = Data(1, 27) + 1

[color green]
'Put all your error checking here eg check all the fields
'are entered correctly

'These are just examples, change them to whatever fields you want...[/color]
Data(Row, 1) = TextName
Data(Row, 2) = ComboDest
Data(Row, 3) = ComboAge
Data(Row, 4) = TextBox3
Data(Row, 5) = TextBox4
Data(Row, 6) = TextBox5
Data(Row, 7) = ListBox1

If optionYes = True Then
Data (Row, 8) = "Yes"
Else
Data (Row, 8) = "No"
End If
[/tt]

N.B.
There is one hitch - if you add a new column to the workbook, the cell with COUNTA(A:A) will be moved to the right 1, and you'll need to adjust the number in the code above.