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:
Option Explicit 'The Row variable isn't just a name, not the 'actual row of cells 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() 'Row references that cell in the worksheet. The (1, 27) is 'row 1, column 27, which is where I said to put it. Row = Data(1, 27) + 1
'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... 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
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.
Talk To Other Members