Excel 2007 User form Enter Data Last Row @Table
 

Hi all new to VBA and have a user form where the person can enter various
sales data into Excel. The data will be entered into a spreadsheet within a
defined Table I.e. @Table1 defined with the new Table feature of 2007. As
the user over time inputs new sales info via the user form it will need to
place in the next available row in the in the @Table1. Can anyone give any
resources where I can find code snippets to modify.

Many thanks for taking the time to look and any and all guidance welcome.

Debbie (UK)

Answer: Excel 2007 User form Enter Data Last Row @Table
 

Hi Debbie,

To add data from a user form to the next available row in a table in Excel 2007, you can use the following VBA code:

1. First, you need to define the table range. You can do this by selecting any cell within the table and then going to the "Design" tab in the ribbon. In the "Tools" group, click on "Name Manager" and then create a new name for the table range.
2. In your user form, create a button or other control that the user will click to add the data to the table.
3. Double-click on the button to open the code editor. In the code editor, add the following code:
   
   
   Formula:

   ---

   Dim tbl As ListObject
Dim newRow As ListRow

Set tbl = ActiveSheet.ListObjects("Table1")
Set newRow = tbl.ListRows.Add

With newRow
    .Range(1) = TextBox1.Value 'replace TextBox1 with the name of the control where the user enters the data
    .Range(2) = TextBox2.Value 'replace TextBox2 with the name of the control for the second column, and so on
    'add more lines for each column in the table
End With 


   ---

4. Save the code and close the editor.
   

Now, when the user clicks on the button, the data entered in the user form will be added to the next available row in the table.

Let me know if you have any further questions.

Excel 2007 User form Enter Data Last Row @Table
 

Sub AddToBottomOfExistingTable()
Dim WS As Worksheet
Dim oList As ListObject
Dim oRow As ListRow

Set WS = ActiveSheet
Set oList = WS.ListObjects(1)
Set oRow = oList.ListRows.Add
oRow.Range.Value = "Sludge"
End Sub
--
My resource was the Excel VBA help file.

Jim Cone
Portland, Oregon USA
Add-in download: http://tinyurl.com/ExtrasForXL




"Debbie D."
wrote in message
Hi all new to VBA and have a user form where the person can enter various
sales data into Excel. The data will be entered into a spreadsheet within a
defined Table I.e. @Table1 defined with the new Table feature of 2007. As
the user over time inputs new sales info via the user form it will need to
place in the next available row in the in the @Table1. Can anyone give any
resources where I can find code snippets to modify.

Many thanks for taking the time to look and any and all guidance welcome.

Debbie (UK)