VLooking Reference in Combo Box

  • Thread starter Thread starter Shauna Koppang
  • Start date Start date
S

Shauna Koppang

I am a rank beginner...

I have created a userform where I have placed a combo
box. I would like the user to be able to see the first
column of a Vlookup table and when they pick the desiret
item, it picks then displays the other items from the
choice into cells.

E.g. Product code in column A, Description in Column B of
VLookup table on Sheet 2

The ComboBox displays the contents of Column A. The user
choose it and it places this choice on Sheet 1 in cell A1
and the description from Column B into Sheet 1 cell B1.
Does this make sense.

So.. how do I code it or where would you recommend I look
for this information? I have ComboBox1 on my userform.

Thanks!
Shauna
 
Shauna,

Go to the VBE and open the dialog box. Right click on the combobox and
select properties. In the properties window go to Control source and type
in Sheet1!A1. Than put a Vlookup formula in cell B1.
Now whenever you show the form and change the combobox, the result will
automatically show in A1, and the formula in B1 will calculate.
 
Hi,

Thanks for that bit of coding. It is a great help and
start. I can get it insert the typed value into the cell
and collect and deposit values from a VLookup table
through the formula. Thanks!!!!

Now I also need it to go one step further.

On Sheet1 I have a combobox that needs to display a list
of vendor codes. I have a VLookup table on containing the
codes in column 1 and the Company name, Address, etc in
the next columns. So what I need is for the userfom to
display in the combobox the list of the vendor codes from
the first column of the VLookuptable and when they choose
it from the list, it then puts that number into a cell,
then the VLoopkup formulas reference that result pulling
the desired info from the remaining columns.

Also, what I also need to have happen is that a user picks
a name of the ordering person displayed in a combobox list
and it enters it into a specified cell. Problem, the
names are in a range on on Sheet2 called Names E.g.
A1:A6 . The supporters of this template don't want to
have to make coding changes as new neames get added, so I
guess I can't create the names in ComboBox.AddItem "ZZ"
format.

I hope these make sense as I am just a beginner at this VB
stuff.

Any suggestions?

Thanks!
Shauna
 
Shauna,

Easier than you thought. Use self-expanding names

Define name with this formula in the refers to box
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A)-1,1)

adjust the sheet name and column as needed. Change $A$1 to $A$2 if you have
a header row.

Now it doesn't matter how short or long the list is.

Go to your form in the VBE right click on the combobox and select
properties. Than for RowSource just type in the defined name. You
shouldn't even need the sheet name reference like before, just the defined
name.
 
Shauna,

This is one of my weak points, but the default should be whatever is in the
ControlSource (B9).

Checkout Combobox1.ListIndex = ***some number***

Double click the userform and

Private Sub UserForm_Initialize()
Userform1.Combobox1.Listindex = 2
End Sub

You could also set this up to accept a variable instead of a number.

You should also be able to show an empty by
Userform1.Combobox1=""
or
Userform1.Combobox1.Text = ""
 
Hi Steve,

Well I did what you suggested but of the 3 ComboBoxes I
only get the ComboBox1 displaying. The others, no matter
what I set the number to they don't display.

Private Sub UserForm_Initialize()
UserForm1.ComboBox1.ListIndex = 1
UserForm1.ComboBox2.ListIndex = 1
UserForm1.ComboBox3.ListIndex = 1
End Sub


I am probably just missing something simple.

Thanks again!!! This is getting fun:-)

Shauna
 
Shauna,

Is the appropriate list in each of the comboboxes?
Can you go down the list(s) when the form is open?

You may have to set the RowSource in the initialize event.
Also in the properties window for each, make sure that the value is either
empty or has the default that you want.

Yes, forms can be fun and powerful. But frustraing until you have a few
under your belt.

Keep Exceling...
 
HI Steve,

I double checked all ComboBoxes. All have the correct
named ranges in the RowSource (two reference the same
list), and all items display properly in each of the lists
and you can type first characters or go down the list when
you run it. I even check the check box settings and they
were exactly the same with the exception of the TabIndex,
RowSource, ControlSource (and of course the name :-)

No Value or Text settings in any ComboBox Properties.

What is the code to initialize the RowSource? If I put it
in do I remove it from properties?

And yes, can be frustrating. Just when you think you got
it, you find out you really don't. I shall just keep
trying :-)

You assistance again is greatly appreciated.

Shauna
 
Shauna,

Glad I was able to give you (some) help.

Looks like we got a bite. Jim chimed in with how to set a default.

So since you weren't getting anything with this I don't know what to say.

But - Keep Exceling...
 
Back
Top