1

I have been using a below piece of code that adds the TextBox1 value into the Col"B" and Col"L" first empty cell of two column 1 is table column and second is range column. Both are from different sheets.

But receiving an error Object does not support this property and range

Any help will be appreciated.

        Private Sub CommandButton1_Click()
        
Sheet37.ListObjects("Table14").ListColumns("Condition").End(xlDown).Offset(1, 0).Value = TextBox1.Value
        
Sheet5.Range("L2").End(xlDown).Offset(1, 0).Value = TextBox1.Value

ActiveCell.Value = TextBox1.Value
        
    Unload Me
        End Sub
Improve this question

1 Answer 1

Reset to default
0

When i saw Unload Me, i am assuming that you are using Userform to write data on excel sheet, in your case, the error occur due to you did not mention where the textbox is located:

All the 3 VBA are missing me. that refer to userform that you have created:

Sheet37.ListObjects("Table14").ListColumns("Condition").End(xlDown).Offset(1, 0).Value = me.TextBox1.Value
        
Sheet5.Range("L2").End(xlDown).Offset(1, 0).Value = me.TextBox1.Value

ActiveCell.Value = me.TextBox1.Value

If you still not clear how to link Userform-textbox-excel, you can refer to my simple instruction on this link Can't update a table with vba

To update the value on ListObject Table, you may use the following method as we need to use databodyrange in order to access the cell in Listobject:

Sub submit()

Dim tb1 As ListObject
Dim s As Long

Set tb1 = Sheet2.ListObjects("Table1")
s = tb1.ListRows.Count

tb1.DataBodyRange.Cells(s + 1, 1) = Me.TextBox1.Value

Unload Me

End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Still receiving same error on this line Sheet37.ListObjects("Table14").ListColumns("Condition").End(xlDown).Offset(1, 0).Value = me.TextBox1.Value @Kin Siang
You may refer to how to update data in excel table on this link stackoverflow.com/questions/58417779/…. When you are writing VBA to update data, by removing the table and directly update value with range is much simple and easier. Accept the answer if helping :)
Yes you are right you have referred me to the links. I have gone through the links but could not find how to fix the above error which still i am Receiving. Sure i will accept this as answer when i will be able to find the error through your links. Do not worry i will accept but still struggling to sort it out, @Kin Siang
ok, let me try how to update value with table, something new for me also
You may see my edited answer, I think quite similar to range data, although you need to set the table at beginning

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.