Macro to change cells' display, Pounds, euro, dollars

  • Thread starter Thread starter markharris2000
  • Start date Start date
M

markharris2000

I have a spreadsheet which I plan to protect all but a handful of
fields for the user to enter various money amounts. I'd like to include
a BUTTON in the spreadsheet that will force the display of any money
field to pounds, dollars, or euro. Mostly a cosmetic change, and I
don't want the user to have to select the fields to change since some
are locked.

Is there any example like this using a button/macro?
 
Mark,

Maybe like this, to change specific cells:

Sub ChangeCurrFormat()
Dim rngCurrency As Range
Dim MyMsg As String
Dim myStyle As Integer

Set rngCurrency = Range("A1,B3,C5,D7")

MyMsg = "1: US Dollars = > $100.00" & Chr(10) & _
"2: UK Pounds = > £100.00" & Chr(10) & _
"3: EU Euros = > ?100.00" & Chr(10) & Chr(10) & _
"Choose a style: 1, 2, or 3...."

myStyle = Application.InputBox(MyMsg, "Style Choice", , , , , , 1)


Select Case myStyle
Case 1: rngCurrency.NumberFormat = "$#,##0.00"
Case 2: rngCurrency.NumberFormat = "[$£-809]#,##0.00"
Case 3: rngCurrency.NumberFormat = "[$?-2] #,##0.00"
End Select
End Sub


Or, to change all currency cells (once you've set the formatting):

Sub ChangeCurrFormatAllCells()
Dim rngCurrency As Range
Dim MyMsg As String
Dim myStyle As Integer
Dim myCell As Range

MyMsg = "1: US Dollars = > $100.00" & Chr(10) & _
"2: UK Pounds = > £100.00" & Chr(10) & _
"3: EU Euros = > ?100.00" & Chr(10) & Chr(10) & _
"Choose a style: 1, 2, or 3...."

myStyle = Application.InputBox(MyMsg, "Style Choice", , , , , , 1)


For Each myCell In ActiveSheet.UsedRange

Select Case myStyle

Case 1: If InStr(1, myCell.NumberFormat, "£") > 0 Or _
InStr(1, myCell.NumberFormat, "?") > 0 Then _
myCell.NumberFormat = "$#,##0.00"

Case 2: If InStr(1, myCell.NumberFormat, "$#") > 0 Or _
InStr(1, myCell.NumberFormat, "?") > 0 Then _
myCell.NumberFormat = "[$£-809]#,##0.00"

Case 3: If InStr(1, myCell.NumberFormat, "$#") > 0 Or _
InStr(1, myCell.NumberFormat, "£") > 0 Then _
myCell.NumberFormat = "[$?-2] #,##0.00"

End Select

Next myCell

End Sub

Note that these macros don't conver the values from one currency to the other, but just change the
formatting.

HTH,
Bernie
MS Excel MVP
 
Those question marks should be Euro signs -

"3: EU Euros = > ?100.00"

In all instances....

I guess the font that I used doesn't translate well in OE.....

HTH,
Bernie
MS Excel MVP


Bernie Deitrick said:
Mark,

Maybe like this, to change specific cells:

Sub ChangeCurrFormat()
Dim rngCurrency As Range
Dim MyMsg As String
Dim myStyle As Integer

Set rngCurrency = Range("A1,B3,C5,D7")

MyMsg = "1: US Dollars = > $100.00" & Chr(10) & _
"2: UK Pounds = > £100.00" & Chr(10) & _
"3: EU Euros = > ?100.00" & Chr(10) & Chr(10) & _
"Choose a style: 1, 2, or 3...."

myStyle = Application.InputBox(MyMsg, "Style Choice", , , , , , 1)


Select Case myStyle
Case 1: rngCurrency.NumberFormat = "$#,##0.00"
Case 2: rngCurrency.NumberFormat = "[$£-809]#,##0.00"
Case 3: rngCurrency.NumberFormat = "[$?-2] #,##0.00"
End Select
End Sub


Or, to change all currency cells (once you've set the formatting):

Sub ChangeCurrFormatAllCells()
Dim rngCurrency As Range
Dim MyMsg As String
Dim myStyle As Integer
Dim myCell As Range

MyMsg = "1: US Dollars = > $100.00" & Chr(10) & _
"2: UK Pounds = > £100.00" & Chr(10) & _
"3: EU Euros = > ?100.00" & Chr(10) & Chr(10) & _
"Choose a style: 1, 2, or 3...."

myStyle = Application.InputBox(MyMsg, "Style Choice", , , , , , 1)


For Each myCell In ActiveSheet.UsedRange

Select Case myStyle

Case 1: If InStr(1, myCell.NumberFormat, "£") > 0 Or _
InStr(1, myCell.NumberFormat, "?") > 0 Then _
myCell.NumberFormat = "$#,##0.00"

Case 2: If InStr(1, myCell.NumberFormat, "$#") > 0 Or _
InStr(1, myCell.NumberFormat, "?") > 0 Then _
myCell.NumberFormat = "[$£-809]#,##0.00"

Case 3: If InStr(1, myCell.NumberFormat, "$#") > 0 Or _
InStr(1, myCell.NumberFormat, "£") > 0 Then _
myCell.NumberFormat = "[$?-2] #,##0.00"

End Select

Next myCell

End Sub

Note that these macros don't conver the values from one currency to the other, but just change the
formatting.

HTH,
Bernie
MS Excel MVP


I have a spreadsheet which I plan to protect all but a handful of
fields for the user to enter various money amounts. I'd like to include
a BUTTON in the spreadsheet that will force the display of any money
field to pounds, dollars, or euro. Mostly a cosmetic change, and I
don't want the user to have to select the fields to change since some
are locked.

Is there any example like this using a button/macro?
Click to expand...
Click to expand...
 
I will try this immediately. Looks like exactly what I was looking for!

thanks,

Mark

Bernie said:
Those question marks should be Euro signs -

"3: EU Euros = > ?100.00"

In all instances....

I guess the font that I used doesn't translate well in OE.....

HTH,
Bernie
MS Excel MVP


Bernie Deitrick said:
Mark,

Maybe like this, to change specific cells:

Sub ChangeCurrFormat()
Dim rngCurrency As Range
Dim MyMsg As String
Dim myStyle As Integer

Set rngCurrency = Range("A1,B3,C5,D7")

MyMsg = "1: US Dollars = > $100.00" & Chr(10) & _
"2: UK Pounds = > £100.00" & Chr(10) & _
"3: EU Euros = > ?100.00" & Chr(10) & Chr(10) & _
"Choose a style: 1, 2, or 3...."

myStyle = Application.InputBox(MyMsg, "Style Choice", , , , , , 1)


Select Case myStyle
Case 1: rngCurrency.NumberFormat = "$#,##0.00"
Case 2: rngCurrency.NumberFormat = "[$£-809]#,##0.00"
Case 3: rngCurrency.NumberFormat = "[$?-2] #,##0.00"
End Select
End Sub


Or, to change all currency cells (once you've set the formatting):

Sub ChangeCurrFormatAllCells()
Dim rngCurrency As Range
Dim MyMsg As String
Dim myStyle As Integer
Dim myCell As Range

MyMsg = "1: US Dollars = > $100.00" & Chr(10) & _
"2: UK Pounds = > £100.00" & Chr(10) & _
"3: EU Euros = > ?100.00" & Chr(10) & Chr(10) & _
"Choose a style: 1, 2, or 3...."

myStyle = Application.InputBox(MyMsg, "Style Choice", , , , , , 1)


For Each myCell In ActiveSheet.UsedRange

Select Case myStyle

Case 1: If InStr(1, myCell.NumberFormat, "£") > 0 Or _
InStr(1, myCell.NumberFormat, "?") > 0 Then _
myCell.NumberFormat = "$#,##0.00"

Case 2: If InStr(1, myCell.NumberFormat, "$#") > 0 Or _
InStr(1, myCell.NumberFormat, "?") > 0 Then _
myCell.NumberFormat = "[$£-809]#,##0.00"

Case 3: If InStr(1, myCell.NumberFormat, "$#") > 0 Or _
InStr(1, myCell.NumberFormat, "£") > 0 Then _
myCell.NumberFormat = "[$?-2] #,##0.00"

End Select

Next myCell

End Sub

Note that these macros don't conver the values from one currency to theother, but just change the
formatting.

HTH,
Bernie
MS Excel MVP


I have a spreadsheet which I plan to protect all but a handful of
fields for the user to enter various money amounts. I'd like to include
a BUTTON in the spreadsheet that will force the display of any money
field to pounds, dollars, or euro. Mostly a cosmetic change, and I
don't want the user to have to select the fields to change since some
are locked.

Is there any example like this using a button/macro?
Click to expand...
Click to expand...
Click to expand...
 
Just tried it. Works great. I'm having trouble displaying EURO symbol,
but I am sure I can figure that out...

Thanks!

Bernie said:
Those question marks should be Euro signs -

"3: EU Euros = > ?100.00"

In all instances....

I guess the font that I used doesn't translate well in OE.....

HTH,
Bernie
MS Excel MVP


Bernie Deitrick said:
Mark,

Maybe like this, to change specific cells:

Sub ChangeCurrFormat()
Dim rngCurrency As Range
Dim MyMsg As String
Dim myStyle As Integer

Set rngCurrency = Range("A1,B3,C5,D7")

MyMsg = "1: US Dollars = > $100.00" & Chr(10) & _
"2: UK Pounds = > £100.00" & Chr(10) & _
"3: EU Euros = > ?100.00" & Chr(10) & Chr(10) & _
"Choose a style: 1, 2, or 3...."

myStyle = Application.InputBox(MyMsg, "Style Choice", , , , , , 1)


Select Case myStyle
Case 1: rngCurrency.NumberFormat = "$#,##0.00"
Case 2: rngCurrency.NumberFormat = "[$£-809]#,##0.00"
Case 3: rngCurrency.NumberFormat = "[$?-2] #,##0.00"
End Select
End Sub


Or, to change all currency cells (once you've set the formatting):

Sub ChangeCurrFormatAllCells()
Dim rngCurrency As Range
Dim MyMsg As String
Dim myStyle As Integer
Dim myCell As Range

MyMsg = "1: US Dollars = > $100.00" & Chr(10) & _
"2: UK Pounds = > £100.00" & Chr(10) & _
"3: EU Euros = > ?100.00" & Chr(10) & Chr(10) & _
"Choose a style: 1, 2, or 3...."

myStyle = Application.InputBox(MyMsg, "Style Choice", , , , , , 1)


For Each myCell In ActiveSheet.UsedRange

Select Case myStyle

Case 1: If InStr(1, myCell.NumberFormat, "£") > 0 Or _
InStr(1, myCell.NumberFormat, "?") > 0 Then _
myCell.NumberFormat = "$#,##0.00"

Case 2: If InStr(1, myCell.NumberFormat, "$#") > 0 Or _
InStr(1, myCell.NumberFormat, "?") > 0 Then _
myCell.NumberFormat = "[$£-809]#,##0.00"

Case 3: If InStr(1, myCell.NumberFormat, "$#") > 0 Or _
InStr(1, myCell.NumberFormat, "£") > 0 Then _
myCell.NumberFormat = "[$?-2] #,##0.00"

End Select

Next myCell

End Sub

Note that these macros don't conver the values from one currency to theother, but just change the
formatting.

HTH,
Bernie
MS Excel MVP


I have a spreadsheet which I plan to protect all but a handful of
fields for the user to enter various money amounts. I'd like to include
a BUTTON in the spreadsheet that will force the display of any money
field to pounds, dollars, or euro. Mostly a cosmetic change, and I
don't want the user to have to select the fields to change since some
are locked.

Is there any example like this using a button/macro?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Converting dollars into euros 6
IF formula for currency or Macro help. 0
how to do multiple IF THEN formulas 3
How do I convert euro to us dollars in excel? 1
How do I calculate and display in British Pounds and/or Euros? 4
Toggle form field between U.S. dollars and Euros 1
Foreign Currency on Reports 1
Can I disply Euro and Dollar Formats in the same spreadsheet 2

Back
Top