Conditional ComboBox

[johnpants]

Hi, I have a combobox on a userform that gets it's info from a seperate workbook (admin). What I would like it to do is that when the user selects one of the entries in the combobox, it automatically fills a textbox with the data directly to the left of the combobox source data..

Additional info:

Combobox = Combobox1
Source for combobox = Book 'Admin' sheet 'Data' range 'D5:D65536'
Source for Textbox fill = Book 'Admin' Sheet 'Data' range 'C5:C65536'
Textbox = Textbox1

Example:
If I select "CIS" in the combobox it will fill the textbox with "Marketing" as Marketing is in the cell to the left of CIS in the source sheet.

Hope this makes sense and somebody is able to help.

Thanks,

John.

 

[colbymack]

You can pre-populate the column with a vlookup statement that looks for the value selected and then returns the text next to it. Will that work for you?

 

[johnpants]

Will this work in a textbox on a userform?

 

[colbymack]

It works great with one catch - the other workbook has to be open. I am looking to see if there are ways around this, other than copying the source workbook into your new one.

If you are OK with the source being open, then reply back and I will explain what I did.

colbymack

 

[johnpants]

At the moment, the source workbook opens so that the ComboBox can get it's information, then closes again. I could change it so that it closes once the user clicks the command button, which would mean that it would be open as you suggested, and would save copying it etc.

Does that help?

 

[Norie]

Does this work?

Textbox1.Value = Range("C" & Combobox1.Listindex+5)

 

[colbymack]

To get this to work, you need to use named ranges in both of your workbooks.

Let's say I have 2 workbooks - 1 called Pricing, that has Sheet1 with all of the partnumber, part description and part pricing on it.
On this file, I have named a range called partdescrprice that includes those three columns (assuming my columns in pricing are Part, description, and price).


Let's also say that I have a separate workbook called Invoice. (the equivalent of your userform). On that sheet I have a named range called partdescrprice. Partdescrprice's name definition is as follows:
=[pricing.xls]Sheet1!$A$2:$C$7

I can then use the following vlookup formula in B2 to find the related description when a partnumber is entered in A2:
=IF(ISERROR(VLOOKUP(A2,partdescrprice,2,FALSE))," ",VLOOKUP(C5,partdescrprice,2,FALSE))
I could also have it return the price in the next column by using the same formula, but replacing the 2 with a 3.

HTH