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!
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Norie

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

ADVERTISEMENT

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,047
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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,047
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,057
Messages
5,569,954
Members
412,300
Latest member
Chaneycr
Top