Currency Conversion

[DanHegarty]

Hi all,

I have a budget spreadsheet which is currently expressed in euros, i would like a pair of option buttons which would allow me to toggle all the figures between euros and dollars.

Anybody have any ideas?

Thanks

[WinteE]

Hi Dan,

I've made you a sample, hope you're familiar with VBA. If not then feel free to ask for help/explanation.

Cheers,
Erik

[Peter1999]

You could use something like this. Use a validation list (this macro is for USD, GBP, EUR), to be selected in E2, and the number you wish to convert in F3. Hope this is what you need.

Peter1999

Sub Macro1()

If (Range("E2") = "EUR") Then

    Range("F3").NumberFormat = "$ #,##0.00_-"
    
Else:
If (Range("E2") = "GBP") Then
    Range("F3").NumberFormat = "[$£-809]#,##0.00"

Else:
If (Range("E2") = "USD") Then
    Range("F3").NumberFormat = "[$$-409]#,##0.00"

Else
End If
End If
End If
End Sub

[DanHegarty]

Hi Guys

Thanks for your help but i'm afraid i'm still struggling.

WintE, when i open your example, the buttons are not functional. I created some new buttons and assigned the appropriate macros to them. Unfortunately when i click on them nothing happens.

Peter, I pasted your code in and then assigned it to a button, but again nothing happened when i pressed the button.

Neither showed any debug faults but nothing happened!

Please let me know if I'm doing something stupid.

Thanks

D

[Peter1999]

Sorry, I read back and understood you were trying to do something else than I proposed. My code will just change the format. Maybe you could combine the two macros....

I tested Eriks' code and it works fine with me. Could you specify what problem shows up?

Peter1999

[WinteE]

Hi Dan, Peter,

The macro security is probably "High". You can change this via 'Tools', 'Macro', 'Security' and select 'Medium'.

Erik

[DanHegarty]

Hi guys,

That works brilliantly now. I was using excel on moc osx and it wasn't working but having tried it on a pc it is fine.

One more problem however, the budget that I am working with is fairly large (roughly 1500 cells). When I use the conversion it locks the computer up for 3 or 4 minutes. This seems crazy given the fact that computers can perform millions of caluclations per second. I can visually see each number change as the macro works along the rows!

One issue may be that there are many blank cells involved, but I want the buttons to cover the whole range in case figures are entered there at a later date.

Really can't say thank you enough.

D