Currency change via vba
 

Help please . . . .

I want a user to be able to select a currency from a drop-down and the following macro will change certain cells to that currency. SO far it's working fine for £, EURO and ¥ (Pounds, Euros and Yen) but if it's changed to $ it does not appear to be working:- the cell's number formats are changed to £ (which is also my default currency format).




Currency_Symbol = Worksheets("Invoice").Range("H6")

Worksheets("Invoice").Range("I25:I42").NumberForma t = Currency_Symbol & "#,##0.00_);(" & Currency_Symbol & " #,##0.00)"



Any help very much appreciated.

Cheers!

Steve

Currency change via vba
 

Hi Steve,

Am Fri, 19 Feb 2016 10:05:56 -0800 (PST) schrieb :

I want a user to be able to select a currency from a drop-down and the following macro will change certain cells to that currency. SO far it's working fine for £, EURO and ¥ (Pounds, Euros and Yen) but if it's changed to $ it does not appear to be working:- the cell's number formats are changed to £ (which is also my default currency format).

Currency_Symbol = Worksheets("Invoice").Range("H6")

Worksheets("Invoice").Range("I25:I42").NumberForma t = Currency_Symbol & "#,##0.00_);(" & Currency_Symbol & " #,##0.00)"

try:

Sub Test()
Dim LRow As Long
'If the currency values are in column A and
'the changed currency is in A1

LRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1").Copy
Range("A2:A" & LRow).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Currency change via vba
 

Hi Steven,

Am Fri, 19 Feb 2016 10:05:56 -0800 (PST) schrieb :

Currency_Symbol = Worksheets("Invoice").Range("H6")

Worksheets("Invoice").Range("I25:I42").NumberForma t = Currency_Symbol & "#,##0.00_);(" & Currency_Symbol & " #,##0.00)"

modify the references:

Sub Neu()
Dim Currency_Symbol As String, myStr As String
Currency_Symbol = Range("H1").Text
myStr = IIf(Currency_Symbol = "$", "\", "") & Currency_Symbol & " #,##0.00;" _
& IIf(Currency_Symbol = "$", "\", "") & Currency_Symbol & " (#,##0.00)"
Range("A1:A25").NumberFormat = myStr
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Currency change via vba
 

Hi Claus

Thanks very much for that, it's plugged straight into my macro and worked a treat.

Steve



On Friday, 19 February 2016 18:50:33 UTC, Claus Busch wrote:
Hi Steven,

Am Fri, 19 Feb 2016 10:05:56 -0800 (PST) schrieb :

Currency_Symbol = Worksheets("Invoice").Range("H6")

Worksheets("Invoice").Range("I25:I42").NumberForma t = Currency_Symbol & "#,##0.00_);(" & Currency_Symbol & " #,##0.00)"

modify the references:

Sub Neu()
Dim Currency_Symbol As String, myStr As String
Currency_Symbol = Range("H1").Text
myStr = IIf(Currency_Symbol = "$", "\", "") & Currency_Symbol & " #,##0.00;" _
& IIf(Currency_Symbol = "$", "\", "") & Currency_Symbol & " (#,##0.00)"
Range("A1:A25").NumberFormat = myStr
End Sub





Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Currency change via vba
 

Wow, this is really useful and convenient!

I myself am a stickler for formatting and consistency.
I prefer the symbols aligned to the left.
So I added an "*" inside Claus's formatting masks:

---- " * #,##0.00;" ---- " * (#,##0.00)"

It make the figures visually more organized.

Cheers,
Mark

Currency change via vba
 

Hi Steve,

Am Sat, 20 Feb 2016 03:07:23 -0800 (PST) schrieb :

Thanks very much for that, it's plugged straight into my macro and worked a treat.

you are welcome.

You could put the Currency symbols in H6 in a líst of the data
validation.
Then the OP has not to run the macro because it can be fired by
Worksheet_Change.
I also changed the string for $ because I guess it is more reliable in
this way:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) < "H6" Then Exit Sub

Dim Cur_Sym As String, myStr As String

Cur_Sym = Target

myStr = IIf(Cur_Sym = "$", "[$$-en-US]", Cur_Sym)
Range("I25:I42").NumberFormat = myStr & "__#,##0.00;" & myStr &
"__(#,##0.00)"
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Currency change via vba
 

Thanks Mark, I shall follow your lead on that!

Steve

Currency change via vba
 

Hi Steve,

Am Thu, 25 Feb 2016 10:24:20 -0800 (PST) schrieb :

Thanks Mark, I shall follow your lead on that!

please read also my last answer


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional