Using Combo Boxes To select Currencies

[Pug205GRD]

After spending some time banging head against my monitor and getting fed up with Google not finding what I want I thought I would turn to you good people to see if you can help me out....

What do I want?

Well at the momonet I am creating a spreadsheet to calculate Gross Profit and GP% of total material costs. The aim is to make this sheet availabe to everybody in my company in read only format so they can add the relevant values for the quotes and jobs that are done. This will give us the opportunity to calculate more accurately just how much (or more likely how little) Gross Profit we are making as a department.

Things have now got a little complicated because although we trade primarily in £, we also purchase in US$ and €. Being an international company we also sell in all 3 currencies.

Our financial and stock managemant systems are written in house and are not very good at converting currenices.

What I want to do is to create a series of pull-down menus (so I can do itemised quotes) that gives the following 5 options:

£ - £ £1.00
£ - $ £1.75
£ - € £1.45
$ - £ £0.57
€ - £ £0.69

The figures are the current conversion rates we use. However these are not static and tend to change once a month depending on current market conditions. We specify the £ - $ and £ - € rate and from that I can calculate the reciprical values for currency conversion going the other way.

I digress. What I want to do is to be able to use a pull down menu where I can select the currency we buy stock in and the currency we sell to our customers in. From this selection I want to be able to insert the conversion rate in the linked cell which will then automatically convert the 'buy by currency' value into £, our native currency. I also want to be able use the same technique so I can quote my customers in either £, $ or €, using the figures generated from first conversion. We do buy certain items in US$ and sell in € and I need to be able to calculate my GP in £!

Does this make sense to anybody? Have I made myseelf clear enough for you to be able to figure out what I want? I am absolutely clueless when it comes to coding and VB etc. so I really am looking for somebody to explain this as if im about 6 years old.

I have got as far as creating the combo box using ActiceX controls and I can select the currenct conversion rate off the list. The next step I am stuck with is to insert a numerical value into the spreadsheet from the conversion phrases I have shown above.

The spreadsheet is currently just 32Kb in size as I have deleted all the experimental combo boxes I have failed with so far. If anybody is feeling particularly generous and would like to create a demo. on my spreadsheet I will be more than greatful to you....

 

[Joe Was]

Off to the right of the active range on that sheet build a Named Range that contains your Convertions. We do this so you can update the list as needed. Then in the cell that you want to select what conversion, add a Data Validation DropDown List, using the Named Range as the Source!

Then in the Sheet module for that sheet, select the "Change" Event and add code to look at the conversion choices, chosen and Use the Select Case structure to update the converted values!

Note: as your actual strings for the chosen converion can change use the "Left" function to only select the unique first part of the chosen dropdown as the Case to test!

 

[Pug205GRD]

Thanks for the speedy reply Joe Was.
I have got the Data | Validation pulldown button working and it is making the rest of the spreadsheet behave itself when I insert some relevant data.
I got a bit lost around the second paragraph though. I have my range of data setup and visible. As soon as we are given an updated currency conversion rate I can insert this manually and it will work the reciprocal value out for me. I am assuming that is what you are gettting at in Para. 2?
What I would really like is for the pull down menu to show this:

£ - £
£ - $
£ - €

but I want the pull down menu to insert the relevant values into cell behind the pulldown so it will automatically calculate the pricing. My objective is to make a spreadsheet that will be used by only 5 people but that is quick, easy and efficienbt to use. If I can remove as much error and confusion it will be a worthwhile excerice.

I have made some combo boxes that use a range of numbers that are relevant but when the pulldown is used it only displays the numerical values. These work well in their own right and I can make the spreadsheet do the calculations I want (and accurately too) but I have to be thinking of other people too.

Having just been thinking about this I have another possible solution, I just dont know how to make it work. Can I use a symbol in a numerical field and make Excel ignore the symbol. For instance, can I make the pulldown menu display $ 1.75 for example, but only insert the 1.75 into the spreadsheet? This would give the user a clue as to which currency we are converting from or to. Is it easy to make Excel 'drop the $ sign'?

 

[Pug205GRD]

Ive just answered my own question (at least for the last bit)

Yes, I can use currency symbols in the pulldown menu provided the relevant cells are formatted properly! It also helps that I select the correct range of coversion rates which I didnt do and wondered where all my profit had suddenly come from!

I like your part of this planet Joe Was, I got married over that way about 6 weeks ago.