Populate a combo box on one sheet with data that resides on another sheet in the same workbook

[Majman33]

Hello,

I am trying to populate a combo box on one sheet with data that is stored on another sheet in the same workbook. The data is stored as a table. The data is a list of products with corresponding information about the product (ie Item Number, Description, etc...). Can anyone help with this? Thank you.

 

[My Aswer Is This]

Since you did not tell us the name of the Combobox
And you did not tell us the name of the two sheets
And did not tell us where the range of values are you want loaded into the Combobox

Try this and change the sheet names and the range to suit your needs.
When asking questions on this forum it is always best to give specific details.

Private Sub CommandButton1_Click()
Sheets("One").ComboBox1.Clear
Sheets("One").ComboBox1.List = Sheets("Two").Range("A1:A20").Value
End Sub

 

[Majman33]

Since you did not tell us the name of the Combobox
And you did not tell us the name of the two sheets
And did not tell us where the range of values are you want loaded into the Combobox

Try this and change the sheet names and the range to suit your needs.
When asking questions on this forum it is always best to give specific details.

Private Sub CommandButton1_Click()
Sheets("One").ComboBox1.Clear
Sheets("One").ComboBox1.List = Sheets("Two").Range("A1:A20").Value
End Sub

Thank you for the quick reply. I apologize for the lack of detail. I tried your code without any luck. This is how my code looked.

Private Sub cmbItems_Click()


Sheets("Cost Calculator New").cmbItems.Clear
Sheets("Cost Calculator New").cmbItems.List = Sheets("ItemList").Range("A1:B20").Value


End Sub

When I ran the code I got the following error: "Run-time Error: '-2147467259 (80004005)': Automation error, Unspecified error"

I have included more details in this post. Thanks again.

I would like to be able to use the combo box to look up Item numbers that are located in the ItemList sheet and then use that Item number to pull various other details associated with that item so that I can calculate the item cost information. The data that is in the ItemList sheet is stored in a table format. I have created a basic drop down list on the Cost Calculator sheet that shows the item numbers when you click on the drop down button. The problem is that there are over 200 items. I would like for the drop down list to show the Item Number along with the Item Description next to it.

The combo box is on a sheet titled "Cost Calculator New" and I have named the combo box "cmbItems" The data that I wish to populate the combo box with is located on a separate sheet in the same workbook titled "ItemList". The values that I would like to populate the combo box are in columns A and B. Column A is titled "ItemNo" and column B is "Item". I would like for the list to be dynamic list so that if/when I add a new item to the ItemList that it automatically shows up in the combo box.

Thanks again for your help.

 

[My Aswer Is This]

This question is now with your added requirements beyond my knowledgebase. I'm sure someone else here at Mr Excel will be able to help you

 

[Majman33]

Thank you for trying. Much appreciated.