userforms and comboboxes

ramasterre

Active Member
Joined
Oct 5, 2004
Messages
253
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!
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,351
Office Version
  1. 365
Platform
  1. Windows
Why not just goto the combobox's properties and enter something like this for the RowSource?

Sheet2!A1:A10
 

ramasterre

Active Member
Joined
Oct 5, 2004
Messages
253
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-known Member
Joined
Apr 28, 2004
Messages
76,351
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Oct 5, 2004
Messages
253
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,351
Office Version
  1. 365
Platform
  1. Windows
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?

Code:
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,351
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Oct 5, 2004
Messages
253
Im getting the error message 1004 'method 'range' of object '_global' failed from the line
Code:
TextBox1.Text = Worksheets("Sheet2" & Range("B" & ComboBox1.ListIndex + 1)).Value
 

Forum statistics

Threads
1,172,240
Messages
5,879,876
Members
433,461
Latest member
Confusedexcelhelp

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