Combobox value

exceldvlpr

New Member
Joined
May 22, 2002
Messages
20
I have a list of names in combobox2, (Input Range is B4:B18, linked cell is H7), that are linked to what ever is chosen in combobox1 (Input Range F4:F8, linked cell is H6) I need a formula that will reference the name that is currently selected in combobox2.

Is there a way to return the value of what's currently displayed in a combobox aside from referencing the input range? I can't figure out a different way to do this because what ever you choose in combobox1 changes what's in combobox2.

Thanks for the help.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
I guess I don't completely understand what your question is. It sounds like you have one ComboBox that has 5 different types of lists. Once of of the lists is selected in ComboBox1, ComboBox2 will show that list. Is that what you are talking about?
 
Upvote 0
That's right, I have a list of Team Leads (Combobox1) and a list of the employees for each Team Lead(combobox2). Which ever lead you select, it will modify the list in combobox2 to just the employees under that lead. In a seperate cell (A14) I'm trying to reference the specific name that is currently displayed in combobox2. Normally I'd just compare the number in the linked cell to the corresponding name in my input range, but because the list of names change based on what Team Lead you choose in the combobox1, the number 2 cell for instance, is not always going to be Sally.
 
Upvote 0
My confusion then is what the specific problem is. Are you having a problem getting the values in ComboBox2 to change? If that is the case, I would suggest you do something like this:

Select Case ComboBox1.Value
Case "Lead 1"
ComboBox2.RowSource = Range("B1:B10")
Case "Lead 2"
ComboBox2.RowSource = Range("C1:C10")
End Select

Does this help?
_________________
Pass on what you have learned. Support this great website by clicking on the sponsor's ads!.
This message was edited by phantom1975 on 2002-10-09 18:04
 
Upvote 0
TRY THE REFERENCE AS FOLLOWS
Sheet1.ComboBox2.Text

CHANGE THE SHEET1 TO WHAT EVER SHEET IT IS ON.
 
Upvote 0
Sorry for the confusion. Here's the story, I have one row of hidden cells in this sheet that reference a number of different visible data cells within the same sheet. Obviously each of the cells in this row has a formula that references one of those visible data cells, (most are formulas like =D14), but since the data I need in cell A14 in the hidden row is being pulled from a combobox, I don't understand what formula I would use to capture it and it's not as simple as referencing the input range (see my last post). Once all the data in this row is populated I have a macro that copies the hidden data row and pastes it to a master spreadsheet on a network drive.
 
Upvote 0
BASED OFF OF WHAT YOU HAVE SAID, YOU NEED THE INFORMATION DISPLAYED IN COMBOBOX2.

THE TEXT IN COMBOBOX2 IS THE LINKED CELL H7
OR IF YOUR USING A MACRO.
DIM THETEXT
THETEXT = Sheet1.ComboBox1.Text
OR
THETEXT = RANGE(SHEET1!H7)
 
Upvote 0
I have emailed you what you are looking for. The reason you were having problems is because you are using the WRONG tools in Excel. You are using the FORMS toolbar when you should be using the CONTROL toolbar. The FORMS toolbar is limited to what it can do.
 
Upvote 0
Thanks to both of you on this reply for the help. I understand the forms functions are a bit simpler to use, however as Phantom mentioned, they are a also more limited in what they have to offer. I'll be investing more time in getting to learn the Control Toolbox functions a little better.
Thanks again for the help!
 
Upvote 0

Forum statistics

Threads
1,212,132
Messages
6,106,127
Members
447,996
Latest member
ANDYADAM

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
Back
Top