Help needed for combo box

[Raul]

Hello,

I am trying to create an invoice/quotation template in excel using a
VBA Control (combo box). I am a basic user in excel so I will try to
explain my particular problem by the following example. I have two
sheets in my workbook. Sheet no1 is the data sheet where I have listed
the parts and their prices. There are more than 200 items in the list
with unique part no. It is something similar to below.

Part No. Description Watts Price
AS001 Light Bulb 60 $2.00
AS002 Light Bulb 40 $1.20
AS003 Light Bulb 25 $1.30
AS004 Tube 50 $3.00

The second sheet is an invoice/quotation sheet where I copy the part no
and its price and other details to calculate the total amount.

Right now I have to search (Ctr+F) for a particular part no in sheet
no1 and then copy the part and its price in the invoice/quotation
sheet(sheet no 2). I thought an easier way to do this would be to
create a VBA combo box in the invoice sheet itself where I can select a
part through the combo box and the information can be selected and
pasted on the invoice sheet itself.

I noticed on a lot of websites that if you enter the first three
alphabets the list jumps automatically to the closest match. For ex:
When choosing a state on any online user form, if we press V then the
list would show Vermont, Virginia etc.. . That's something I would
like to incorporate in my excel sheet and I assume VBA controls would
be the solution. I could be wrong on that as there are various ways to
solve a problem in excel.

I hope I stated my problem as clear as possible. Like I said before, my
knowledge of excel is basic and I am struggling to connect my combo box
with my data sheet. Any help/advice on this issue is highly
appreciated. Any useful weblinks/templates would also help.

Thank you in advance.

Raul

 

[Guest]

Raul,
One option might to use Data Validation rather than a combobox.
On your Invoice/quotation select cells where Part Number will appear and then
do "Data==.Data Validation==(Allow => List ==>(Source your list of parts
in sheet1 e.g =PartsRng where PartsRng is a named range corresponding the the
Parts in Sheet1.

In the column requiring the price you could use a VLOOKUP formula to get the
price associated with the selected part.

If you want help with this, post your workbook
([email protected]) and I'll add some code.

HTH