• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to input data from userform into new table row?

H

Hugo huijer

Member
Hi Chandoo,

I just recently learned how to add data from a userform into a data range, while adding a new empty row for each new data.

I now want to store this data in a table, instead of a simple range. I don't know what the code should look like. The method I used for entering the data in a simple range does not work, cause the table will not expand when a new row is entered. I need my sheet to input a new row (in the table) for each time data is entered in the userform. This data will then be entered in the new row.

Can anyone help me with this?

My current code looks like this. This worked for entering the data for a data range, not a table.

Code: 
Worksheets("REGISTER PROJECT").Range("B26").EntireRow.Copy
Worksheets("REGISTER PROJECT").Range("B26").Offset(1).EntireRow.Insert Shift:=xlDown

Application.CutCopyMode = False
   
    With Worksheets("REGISTER PROJECT").Range("B26")
        .Offset(0, 0).Value = Me.cmbTypeWork.Value
        .Offset(0, 1).Value = Me.txtActivity.Value
        .Offset(0, 2).Value = Val(Me.txtBudget.Value)
        .Offset(0, 3).Value = Me.txtDesc.Value
    End With
   
MsgBox "This activity budget has been entered in the registry!", vbOKOnly, "Project variation budget input"
 
This syntax should help you out.
Code: 
Sub AddToTable()

With Range("B25")                 'Where B25 is a HEADER cell within the table
     .ListObject.ListRows.Add (1)
    .Offset(1, 0).Value = "Sue"
    .Offset(1, 1).Value = 10
End With
End Sub
 
Hi,

Thanks for the reply. When I try this code, I get an error saying the operation is not allowed, because the operation is attempting to shift cells in a table on your worksheet.
 
Hi Luke ,

What you have posted is certainly the cause of the error , but I'd like to say that your code will always insert the new row as the first row of the table ; if it is coded as :

.ListObject.ListRows.Add

without the (1) , then the new row will be added at the end of the table.

I assume that going by the code which OP has posted , row 26 is the last row , which is to be copied to the newly added row. The code can then be :
Code: 
With Worksheets("REGISTER PROJECT").Range("B26")
    .ListObject.ListRows.Add
    .Offset(1, 0).Value = Me.cmbTypeWork.Value
    .Offset(1, 1).Value = Me.txtActivity.Value
    .Offset(1, 2).Value = Val(Me.txtBudget.Value)
    .Offset(1, 3).Value = Me.txtDesc.Value
End With
 
MsgBox "This activity budget has been entered in the registry!", vbOKOnly, "Project variation budget input"
Narayan
 
Hi Narayan,

Hmm, you're right. I had looked the the insert as meaning append to beginning of table, but now I am not so sure. Will have to wait for more detail from OP...:confused:
 
Hi guys!

Thanks for your help and sorry for my late reply.

I've tried your Narayan, but I still get the same error. It may be because there is indeed another table located below the one I'm adding data to. Is this the cause of the problem, and is there a solution to it?

Thanks heaps!
 
I think I have figured it out.

There was another table under the table that data was submitted to. This other table was bigger (more columns). I think it gave me the error message because a new row would be created for a smaller amount of columns then the actual width of the other table, thus switching the columns in the other table. Hope that makes sense! ;)

What I've done now: I just made the tables the same size (width in columns) by adding some 'dummy' columns to the above table, to match up with the size of the below table. It doesn't look pretty, but it works now! Thanks for your help.

Small question: is there anyway I can 'hide' the dummy columns of the above table? While keeping the size of the table the same, just so it would look prettier?
 
Hi Hugo ,

Thanks for the feedback.

If your dummy columns are all on the extreme right and not in between , then surely you can select those columns , and change the font of the headers to white so that at least for display purposes , they are invisible ?

Narayan
 
You must log in or register to reply here.
Back
Top