How to automatically change the currency format in a cell

bcfaigg

Board Regular
Joined
Dec 1, 2005
Messages
78
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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,815
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Dec 1, 2005
Messages
78
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

Well-known Member
Joined
Jun 7, 2011
Messages
1,815
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Dec 1, 2005
Messages
78
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:
VBA Code:
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

Well-known Member
Joined
Jun 7, 2011
Messages
1,815
Office Version
  1. 2010
Platform
  1. Windows
Glad your problem is solved.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,252
Messages
5,600,543
Members
414,386
Latest member
PARAMATHMA SENTHILNATHAN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top