How to extract data from another speadsheet into a Combo Box in Excel

[sc11pio]

Hi,

I need help on the above. I have 2 Excel spreadsheets, one of the spreadsheet contains all the data. And the other one is actually a form. In the form itself, I have created a combo box. What I wanna do is, to extract the data from the other spreadsheet, so that whenever I click on the combo box, I will be able to select any value.

Please help!!!

Thanks in advance. =)

 

[Bowers74]

The following code example will fill a ComboBox named "ComboBox1" with the values from "Sheet1" in the cells from A1 to the last cell in the list (each time you add a value to the list, the ComboBox will pick it up.).

Private Sub UserForm_Initialize()
Dim cbRange As Range
Dim cbRangeLastRow As Long
cbRangeLastRow = Sheets("Sheet2").Range("A1").End(xlDown).Row
Set cbRange = Sheets("Sheet2").Range("A1:A" & cbRangeLastRow)
ComboBox1.RowSource = cbRange.Address
Set cbRange = Nothing
End Sub

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[sc11pio]

Mike,

I found the following error after run the code:

Run time error - '9'
Subscription out of range

Let me explain to you what I have,

2 Excel files : Data.xls & Form.xls

All the data is in column A (Data.xls)... the combo is created in Form.xls

I had pasted the code in Form.xlsm and edited the coding as below, pls correct me.

----------------------------------------------------
Private Sub UserForm_Initialize()
Dim cbRange As Range
Dim cbRageLastRow As Long
cbRangeLastRow = Sheets("Data&quot.Range("A1&quot.End(xlDown).Row
Set cbRange = Sheets("Data&quot.Range("A1:A" & cbRangeLastRow)
ComboBox1.RowSource = cbRange.Address
Set cbRange = Nothing
End Sub
----------------------------------------------------

Hope to hear from U =)

 

[Bowers74]

Are you using a UserForm that was created in VBA or have you created a "Form" on an Excel spreadsheet and named it Form.xls?

If you are using a spreadsheet, then the code above won't work for you, because it is meant to be used with a VBA UserForm.

If you are using a Combobox on a worksheet I need to know from which toolbar you took the ComboBox (Forms or COntrol Toolbox).

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[sc11pio]

The Combo box is created using the Control Toolbox. The Combo box should only list down the values that are hardcoded, i.e. enduser will not be allowed to enter new value into the combo list.

As this file will be presented as a form to the user. How should I place the range of data from being displayed in the same file? Pls advise if you have a better idea to create form in Excel, or probably other MS Office programs.

 

[Bowers74]

Enter Design Mode and Right-click on the ComboBox, select Properties. In the properties menu, find the property ListFillRange and enter something like "Sheet2!$A$1:$A$10" into it. This means that all of the values on Sheet2, in cells A1:A10 will be in the ComboBox.

You can use a range from a hidden sheet, or you can even hide a column on the sheet where the ComboBox is located.

I hope that this is what you wanted!

Sorry for the misundrestanding.



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[sc11pio]

Thankyou so much Mike!!! I've got what I wanted.

" U R VERY HELPFULL "

Cheers,
Pio