Problems writing data into a table using a form

Posts: 1
Joined: 03 Feb 2010, 16:52

Problems writing data into a table using a form

Post by ramu »

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.


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
  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 = ""

End Sub
Last edited by HansV on 03 Feb 2010, 17:10, edited 1 time in total.
Reason: to add code ... /code tags

User avatar
Posts: 78629
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Problems writing data into a table using a form

Post by 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.
Best wishes,

User avatar
Jan Karel Pieterse
Microsoft MVP
Posts: 656
Joined: 24 Jan 2010, 17:51
Status: Microsoft MVP
Location: Weert, The Netherlands

Re: Problems writing data into a table using a form

Post by 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" 
    End With 
End Sub

Jan Karel Pieterse
Excel MVP