From Userform to different tables in the same worksheet

udexcel

New Member
Joined
Jun 16, 2021
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
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
 

Attachments

  • VBA compile error.jpg
    VBA compile error.jpg
    192.8 KB · Views: 6
  • Table sample.jpg
    Table sample.jpg
    223.5 KB · Views: 6
  • Userform ALL USAGES.jpg
    Userform ALL USAGES.jpg
    162.2 KB · Views: 6

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

fadee2

Active Member
Joined
Nov 7, 2020
Messages
367
Office Version
  1. 2019
Platform
  1. Windows
Hi udexcel,

Welcome to the Board.

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

with the following piece of code.....

VBA Code:
Set table_list_object = the_sheet.ListObjects(UserForm1.statuscombobox.Value)


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

hth.....
 

udexcel

New Member
Joined
Jun 16, 2021
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
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.....

VBA 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

Active Member
Joined
Nov 7, 2020
Messages
367
Office Version
  1. 2019
Platform
  1. Windows
At which line does the code generates error?
Can you provide your resulting code?
 

udexcel

New Member
Joined
Jun 16, 2021
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
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
 

Attachments

  • statuscombobox error.jpg
    statuscombobox error.jpg
    109.3 KB · Views: 0

fadee2

Active Member
Joined
Nov 7, 2020
Messages
367
Office Version
  1. 2019
Platform
  1. Windows
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
VBA Code:
Set table_list_object = the_sheet.ListObjects(UserForm1.statuscombobox1.Value)

hth
 

Forum statistics

Threads
1,141,062
Messages
5,704,054
Members
421,325
Latest member
tapete86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top