Hi all,

I'm trying to build a very simple macro that returns the exchange rate based on the two inputs from the user - the currency they're selling and the currency they wish to buy. I know that this tool can very simply be built in Excel without resorting to VBA, but for other reasons I need to have it in VBA. So here are the facts:

- I have created a UserForm with two ComboBoxes and one TextBox
- The first ComboBox (cmbCCY1) is for the user to choose the currency they're selling adn the second ComboBox (cmbCCY2) is for the the user to choose the currency they wish to buy
- The TextBox (txtFXrate) is waiting for the Change trigger from cmbCCY1 and once it is triggered it should combine the values from both ComboBoxes and VLOOKUP the rate for the currency combination from the pre-populated table sitting on worksheet "Rates".

Here is the code I have so far:

Private Sub cmbCCY1_Change()

If Len(cmbCCY1.Text) = 3 Then 'if a currency is selected the length of text would be 3 letters
    txtFXrate.Value = Application.WorksheetFunction.VLookup("cmbCCY1.Value" & "cmbCCY2.Value", Worksheets("Rates").Range("A3:B11"), 2, False)
End If

End Sub
However, when I run I get an error. I'm pretty sure where I'm going wrong is with the syntax of the lookup value. To derive the lookup value I have to use the values of both ComboBoxes. For example, if the user chooses USD value in first ComboxBox and then EUR in the second ComboBox, my lookup value needs to be USDEUR to enable me to carry out the lookup.

Please help - thanks