Currency switching

[Majkie79]

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

 

[craig.penny]

Try this

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]

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

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]

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?

 

[Majkie79]

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

Thanks a lot

Mike

 

[craig.penny]

Mike,

This is currently working for me:

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]

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 . Currency symbol works fine, not the format.

Sorry for bothering you again..

Mike

 

[craig.penny]

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. Have you ever encountered this problem with getting all of the numbers to show before?

 

[Majkie79]

Craig,

it started to work in combination with my previous conditional formating. 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

 

[craig.penny]

No problem!