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.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
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?
 

exceldvlpr

New Member
Joined
May 22, 2002
Messages
20
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.
 

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
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
 

jdavis9

Active Member
Joined
Mar 8, 2002
Messages
337

ADVERTISEMENT

TRY THE REFERENCE AS FOLLOWS
Sheet1.ComboBox2.Text

CHANGE THE SHEET1 TO WHAT EVER SHEET IT IS ON.
 

exceldvlpr

New Member
Joined
May 22, 2002
Messages
20
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.
 

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962

ADVERTISEMENT

I apologize, but I guess I still don't quite understand what you need. If you want to email your spreadsheet to me, I'll take a look at it. billy.rowe@cox.net
 

jdavis9

Active Member
Joined
Mar 8, 2002
Messages
337
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)
 

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
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.
 

exceldvlpr

New Member
Joined
May 22, 2002
Messages
20
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,668
Messages
5,597,463
Members
414,145
Latest member
lonnie451

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