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.

Jobs

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

My Archive

Resources

Close Box

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close