From Userform to different tables in the same worksheet

[udexcel]

I have a userform where its data populate in different tables of the same worksheet. A combox in the Userform has lists of table names and I want any table name selected from the combobox to set focus the particular table the data will be populated.

I have INVENTORY1, INVENTORY2, INVENTORY3 and INVENTORY4 as table names represented in combobox1.

When I run my userform My VBA code below shows Compile error: Method or data member not found and .ListObject highlighted blue along side Private Sub CommandButton1_Click() in yellow color.

PLEASE I NEED HELP.

My codes below:
Private Sub CommandButton1_Click()

Dim the_sheet As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow
Set the_sheet = Sheets("ALL USAGES")
Set table_list_object = the_sheet.ListObject("INVENTORY1", "INVENTORY2")

Set table_object_row = table_list_object.ListRows.Add

If statuscombobox1.Value = "INVENTORY1" Then
ListObjects = "INVENTORY1"

Else

If statuscombobox1.Value = "INVENTORY2" Then
ListObjects = "INVENTORY2"

Else

If statuscombobox1.Value = "INVENTORY3" Then
ListObjects = "INVENTORY3"

Else

If statuscombobox1.Value = "INVENTORY4" Then
ListObjects = "INVENTORY4"

End If
End If
End If
End If

table_object_row.Range("INVENTORY1", 1).Value = ComboBox1.Value
last_row_with_data = the_sheet.Range("A" & Rows.Count).End(xlUp).Row
Sheets("ALL USAGES").Cells(lastrow + 1, "A").Value = TextBox1.text
Sheets("ALL USAGES").Cells(lastrow + 1, "B").Value = TextBox2.text
Sheets("ALL USAGES").Cells(lastrow + 1, "C").Value = TextBox3.text
Sheets("ALL USAGES").Cells(lastrow + 1, "D").Value = TextBox4.text
Sheets("ALL USAGES").Cells(lastrow + 1, "D").Columns.AutoFit
MsgBox ("Data is added successfully")
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
ComboBox1.Value = ""
Worksheets("DASHBOARD").Activate
Worksheets("DASHBOARD").Cells(1, 1).Select
End Sub

Private Sub UserForm_Initialize()
ComboBox1.Value = SetFocus
TextBox2.text = Date
ComboBox1.List = Array("INVENTORY1", "INVENTORY2", "INVENTORY3", "INVENTORY4")

End Sub

 

[fadee2]

Hi udexcel,

Welcome to the Board.

Instead of using the IF statement to evaluate combobox value, and setting the listobject, try the replacing

with the following piece of code.....

Set table_list_object = the_sheet.ListObjects(UserForm1.statuscombobox.Value)

This should automatically set your desired table as your list object.

hth.....

 

[udexcel]

Hi udexcel,

Welcome to the Board.

Instead of using the IF statement to evaluate combobox value, and setting the listobject, try the replacing
View attachment 41603
with the following piece of code.....

Set table_list_object = the_sheet.ListObjects(UserForm1.statuscombobox.Value)

This should automatically set your desired table as your list object.

hth.....

Thanks so much.

I have replaced my code with yours, but it is still saying compilation error.

You can look into my codes again to pin point issues.

Thanks Fadee2

 

[fadee2]

At which line does the code generates error?
Can you provide your resulting code?

 

[udexcel]

At which line does the code generates error?
Can you provide your resulting code?

Private Sub CommandButton1_Click() ======== Here is underlined in yellow color

Dim the_sheet As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow
Set the_sheet = Sheets("ALL USAGES")
Set table_list_object = the_sheet.ListObjects(UserForm1.statuscombobox.Value) ========= statuscombobox is highlighted blue
Set table_object_row = table_list_object.ListRows.Add

See attached image.


Please look into my codes again to pin point issues.

Thanks for your time so far

 

[fadee2]

My bad, I missed out on combo box control name......

If statuscombobox1.Value = "INVENTORY1" Th

Replace
Set table_list_object = the_sheet.ListObjects(UserForm1.statuscombobox.Value) ========= statuscombobox is highlighted blue

with

Set table_list_object = the_sheet.ListObjects(UserForm1.statuscombobox1.Value)

hth