userforms and comboboxes

[ramasterre]

I've been looking all over the site and I just cant find the answer...

I'm sure this is a simple question. In VBA editor I am creating a user form which has a combobox. I want the combobox's list to populate based on values in a particular column on a non-active sheet. How can I write this out for vba?

Thanks!

 

[Norie]

Why not just goto the combobox's properties and enter something like this for the RowSource?

Sheet2!A1:A10

 

[ramasterre]

Thanks alot. I knew it was simple, but Im feeling a little unintelligent today

 

[ramasterre]

One more question though...how do i get the combobox to only list cells that have a value? The sheet that it will be pulling from will be updated periodically, but for now only has a few values.

 

[Norie]

Well you would need a different method to populate it.

What data do you actually want in it?

Are there gaps in the data, or is the list expanding/contracting day by day?

 

[ramasterre]

Well if your familiar with equities, column A in the sheet has the equity's cusip (identifying number) and column B has the description (name). I would like the combobox that I have to pull from column A every line that is populated. The list of equities on the sheet will expand and contract over time in small increments (doubtful it will be more than one or two items at a time). Also (since I've got your attention) I'd like to have another textbox on my form populate with the corresponding value in column B, such that if the user chooses the combobox value that resides in A9, the textbox will automatically fill with the value from B9.

Think you can help??

Thanks!

 

[Norie]

Yes.

But I would need more information and you haven't quite answered my questions.

I really need to know if the range you want to populate the combobox from is contiguous.

Also are there headers on the sheet with the data?

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim LastRow As Long

     Set ws =Worksheets("Sheet2")
     Lastrow = ws.Range("A" & Rows.Count).End(xlUp).Row
     Combobox1.RowSource = "'" & ws.Name & "'!A1:A" & LastRow
End Sub

Private Sub ComboBox1_Change()
    TextBox1.Text = Worksheets("Sheet2" & Range("B" & ComboBox1.ListIndex + 1).Value
End Sub

 

[ramasterre]

Yes, the data is contiguous and it has headers. Sorry if Im not being clear.

 

[Norie]

Well give a try to the code I posted.

It probably won't work exactly as you want put hopefully it'll point you in the right direction.

The only thing I can add is you might want to change the +1 to +2 in the combobox change event.

Post back with further details if needed.

 

[ramasterre]

Im getting the error message 1004 'method 'range' of object '_global' failed from the line

TextBox1.Text = Worksheets("Sheet2" & Range("B" & ComboBox1.ListIndex + 1)).Value