INTELLIGENT WORK FORUMS FOR COMPUTER PROFESSIONALS
Log In
Come Join Us!
Are you a Computer / IT professional? Join Tek-Tips Forums!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Posting Guidelines
Promoting, selling, recruiting, coursework and thesis posting is forbidden. Students Click Here
|
VBA Visual Basic for Applications (Microsoft) FAQ
VBA How To
Insert data (from a form) into the next empty row on a worksheet by JESTAR
Posted: 7 Aug 03
|
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. |
Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum |
|
|
|
Join Tek-Tips® Today!
Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.
Here's Why Members Love Tek-Tips Forums:
Talk To Other Members
- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close