Currency switching

Majkie79

New Member
Joined
Mar 31, 2011
Messages
6
Hi, I would really appreciate any help with that:
- I have an form with some calculations in iwith some currency format: f.e: in $
- I created a check box which can be checked or not, of course
- if I check the box the currency format should be changed to british pounds.

- I tried to find some solutions with help of conditional formating, coulour formating of cells according to check box status work fine, not the currency format

Any clue?

Thanky you very much in advace.

Mike
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

craig.penny

Well-known Member
Joined
May 8, 2009
Messages
656
Try this

Code:
Private Sub CheckBox1_Click()
      
Dim Rng1 As Range
Set Rng1 = ActiveSheet.Cells(1, 1) 'change to suit your application
If Me.CheckBox1.Value = True Then Rng1.NumberFormat = "[$£-809]#,##0.00"
If Me.CheckBox1.Value = False Then Rng1.NumberFormat = "$#,##0.00"
      
End Sub

This code needs to be in the code module behind the worksheet with the checkbox and it assumes your checkbox name is CheckBox1.

Let me know if it works for you.
 

Majkie79

New Member
Joined
Mar 31, 2011
Messages
6
Hi Craig,

thanks a lot for your replay and your help. I am not very good in that I am afraid.

Could you PLEASE, PLEASE try to modify your code in order to:
- currency changes should be applied in order to CheckBox18
- cells ranges on three sheets to be affected: Fakrurace!E17:G19;Fakturace!G20; FAV!F27;FAV!H27:J29;FAV!H33:J33; DL!I20:J58;DL!J59
- that should be all
- how to implement the code to the file?


I can understand that it has to be annoying to answer to somebody like me, but it will really help a lot.

Thanks a LOT in advance.

Mike

Try this

Code:
Private Sub CheckBox1_Click()
 
Dim Rng1 As Range
Set Rng1 = ActiveSheet.Cells(1, 1) 'change to suit your application
If Me.CheckBox1.Value = True Then Rng1.NumberFormat = "[$£-809]#,##0.00"
If Me.CheckBox1.Value = False Then Rng1.NumberFormat = "$#,##0.00"
 
End Sub

This code needs to be in the code module behind the worksheet with the checkbox and it assumes your checkbox name is CheckBox1.

Let me know if it works for you.
 

craig.penny

Well-known Member
Joined
May 8, 2009
Messages
656
It's not annoying at all!:)

Is the first sheet named Fakrurace or Fakturace?

Is this a form control which looks like a black square on top of your cells or is it an active X control which looks like a sunken grayish square?
 
Last edited:

Majkie79

New Member
Joined
Mar 31, 2011
Messages
6

ADVERTISEMENT

Sorry, sheet name is definitely Fakturace!
It is From control, not Active X Control

Thanks a lot

Mike
 

craig.penny

Well-known Member
Joined
May 8, 2009
Messages
656
Mike,

This is currently working for me:

Code:
Public Sub CheckBox18_Click()
 
Dim Rng1 As Range
Set Rng1 = Sheets("Fakturace").Range("E17:G19,G20")
Dim Rng2 As Range
Set Rng2 = Sheets("FAV").Range("F27,H27:J29,H33:J33")
Dim Rng3 As Range
Set Rng3 = Sheets("DL").Range("I20:J58,J59")
Dim CB1 As CheckBox
Set CB1 = ActiveSheet.CheckBoxes(Application.Caller)
If CB1.Value = 1 Then 'This is for when the checkbox is checked
      Rng1.NumberFormat = "[$£-809]#,##0.00"
      Rng2.NumberFormat = "[$£-809]#,##0.00"
      Rng3.NumberFormat = "[$£-809]#,##0.00"
Else 'this happens when the checkbox is unchecked
      Rng1.NumberFormat = "$#,##0.00"
      Rng2.NumberFormat = "$#,##0.00"
      Rng3.NumberFormat = "$#,##0.00"
End If
End Sub

This should go in a standard module. You will have to assign it to your button.
 

Majkie79

New Member
Joined
Mar 31, 2011
Messages
6

ADVERTISEMENT

Hi Craig,

it WORKS!!! :)

I have only problem with the right currency format. I have adjusted your code by the format I acctualy needed:
"[$€-2]#,##0,00" for when CheckBox is checked
"#,##0,00 Kč" for unchecked

In my question I used british pounds and dollars for not to be even more complicated. I wasn't sure that you can see all special characters on your PC - especialy "č", letter "c" with hook above it. Kč - currency symbol for czech krown, currency of Czech republic.

And my included currency format doesn't work properly. Instead of format:

12,34 Kč or € 12,34 appears like that:
012 Kč or € 012

No decimals :confused:. Currency symbol works fine, not the format.

Sorry for bothering you again..

Mike
 

craig.penny

Well-known Member
Joined
May 8, 2009
Messages
656
I'm glad it's working!:)

For the decimal point/comma problem, I don't really know how to help you. I'm guessing you've already recorded a macro while formatting a cell in the manner you want and that's where you got the code for it. If that's the case, then I'm out of guesses.:confused: Have you ever encountered this problem with getting all of the numbers to show before?
 

Majkie79

New Member
Joined
Mar 31, 2011
Messages
6
Craig,

it started to work in combination with my previous conditional formating:confused:. I am not able to explain why, I am affraid, but it works and this is the core value:).

Thank you very much again for all your patience and all your help of course.

Have a nice rest of the day.

Mike
 

Watch MrExcel Video

Forum statistics

Threads
1,127,554
Messages
5,625,474
Members
416,109
Latest member
TripleA00123

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