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

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?
 
B

Bernie Deitrick

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
 
B

Bernie Deitrick

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...
 
M

markharris2000

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...
 
M

markharris2000

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

MS365 WORD: How to Create Macro Button to CLEAR ALL fields in fill-in form 1
How do I convert euro to us dollars in excel? 1
Subtracting euro in word formulea 1
Convert to Euro - Accounting Format 0
Excel buttons change macro file name when run on other computers 7
how to change from dollars to pounds in a balance sheet 5
How to record a macro within a macro 9
Formatting cells to display numbers in euro style 1

Top