Conditional ComboBox

johnpants

New Member
Joined
Oct 31, 2005
Messages
44
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

Active Member
Joined
Jul 14, 2005
Messages
333
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?
 

colbymack

Active Member
Joined
Jul 14, 2005
Messages
333
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

New Member
Joined
Oct 31, 2005
Messages
44
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,288
Does this work?
Code:
Textbox1.Value = Range("C" & Combobox1.Listindex+5)
 

colbymack

Active Member
Joined
Jul 14, 2005
Messages
333
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
 

Forum statistics

Threads
1,078,532
Messages
5,341,020
Members
399,410
Latest member
mgschainey

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top