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
