How to automatically change the currency format in a cell

[bcfaigg]

Hi,
I have built an Excel (Personal Account dealing approval request) form which is used by circa 600 individuals based across the UK and Europe to submit PA dealing requests.

A number of the cells (share price, total consideration, etc.) have conditional formatting to automatically select £ or € depending on which country the individual is in. This works well, except when an individual in e.g. the Netherlands is buying a share that is denominated in a currency other than €. Basically, the conditional formatting does not allow the user to simply key in e.g. £1,500 - it then changes it back to €1,500...

In short, I would like the cell(s) to default to the 'home' currency of the user, but to allow the user to key in an amount in a different currency and have the cell automatically change to that currency format (most likely to be £ or $).

Clearly the individual could manually use the formatting button at the top - but I was hoping to find a way for the cell currency format to simply update dynamically.

There is plenty of VBA running behind the scenes to route the requests to different approvers and capture relevant details in a register behind the scenes - so maybe someone has come across some simple VB code that I can customise...

Thanks in advance for any suggestions.
Bcfaigg

 

[yky]

In short, I would like the cell(s) to default to the 'home' currency of the user, but to allow the user to key in an amount in a different currency and have the cell automatically change to that currency format (most likely to be £ or $).

How does Excel know when to use the default currency and when to use user's currency?

 

[bcfaigg]

In a previous cell in the form, the user indicates which country they are in - so if they select e.g. the Netherlands, then I have it so that conditional formatting defaults the currency to €. But this causes problems if they are trading shares listed in a country with a different currency - i.e. I am looking or a way to allow the user to key in e.g. $2,000 and have the cell format change to US$...

Thoughts?
Bcfaigg

 

[yky]

When you said "key in $2,000", does the user actually key in the dollar sign? If so, then it's a text string and the conditional formatting probably won't apply. I guess you want a numeric value for further manipulation. If I'm right, then, the question is how can a user enter a numeric value and have it displayed in different currency format? Excel cannot do that. Excel needs an indication to determine what currency format to display. However, if the user actually enters "$2,000", you can write a VBA to 1) check the first letter to decide what conditional formatting to use, and 2) convert the text string to a numeric value.

 

[bcfaigg]

Good question. I had never noticed that unlike £ and €, Excel (in the UK at least) doesn't recognise $ as a currency symbol.

However, easily fixed with some code along the lines of:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As String
Dim Amount As Double
Dim Decimals As Boolean

x = Target.Text
If Left(x, 1) = "$" Then
    Application.EnableEvents = False
    If InStr(x, ".") Then Decimals = True Else Decimals = False
    Amount = CDbl(Right(x, Len(x) - 1))
    If Decimals = True Then Target.NumberFormat = "[$$-en-US]#,##0.00" Else Target.NumberFormat = "[$$-en-US]#,##0"
    Target.Value = Amount
    Application.EnableEvents = True
End If

End Sub

 

[yky]

Glad your problem is solved.