Problems writing data into a table using a form

[ramu]

Hello,
I've created a user form using Excel 2007 to enter data in a worksheet and everything works fine as long as I write my data into the defined range of the worksheet. But when I convert the data range into a table, the data entered using the form are put outside the table. What's wrong. Many thanks in advance for any help.

Ramu

This is the VBA code:

Code: Select all

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Offres")

'find  first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.txtCmpny.Value) = "" Then
  Me.txtCmpny.SetFocus
  MsgBox "Veuillez introduire une offre !"
  Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtCmpny.Value
ws.Cells(iRow, 2).Value = Me.txtFName.Value
ws.Cells(iRow, 3).Value = Me.txtName.Value
ws.Cells(iRow, 4).Value = Me.txtProduct.Value
ws.Cells(iRow, 5).Value = Me.txtSubBy.Value
ws.Cells(iRow, 6).Value = Me.txtDate.Value
ws.Cells(iRow, 7).Value = Me.txtOValue.Value
ws.Cells(iRow, 8).Value = Me.txtDelai.Value
ws.Cells(iRow, 9).Value = Me.txtConclusion.Value
ws.Cells(iRow, 10).Value = Me.txtIndex.Value
ws.Cells(iRow, 11).Value = Me.txtStatus.Value

'clear the data
Me.txtCmpny.Value = ""
Me.txtFName.Value = ""
Me.txtName.Value = ""
Me.txtProduct.Value = ""
Me.txtSubBy.Value = ""
Me.txtDate.Value = ""
Me.txtOValue.Value = ""
Me.txtDelai.Value = ""
Me.txtConclusion.Value = ""
Me.txtIndex.Value = ""
Me.txtStatus.Value = ""
Me.txtCmpny.SetFocus

End Sub

[HansV]

Welcome to Eileen's Lounge!

Just filling cells below the table using VBA will not add a new row to the table, unlike the interactive behavior. Take a look at Jan Karel Pieterse's article Excel 2007 Tables (VBA). The section labeled Inserting rows and columns shows how you can add a row to the table and populate it.

[Jan Karel Pieterse]

I posted an example on the page Hans referred to:

This adds a new row at the bottom of your list and enters a number in each of its cells:

Code: Select all

Sub AddRow2List() 
    Dim oNewRow As ListRow 
    Dim oCell As Range 
    With ActiveCell.ListObject 
        Set oNewRow = .ListRows.Add 
        For Each oCell In oNewRow.Range 
            oCell.Value = "1" 
        Next 
    End With 
End Sub