We help IT Professionals succeed at work.

Changing Currency in a Graphic (Axis)

Bright01
Bright01 asked
on
33 Views
Last Modified: 2015-10-19
I have a nice Currency sign conversion solution that Roy_Cox showed me that allows for me to change all of the currency signs in my workbook.  HOWEVER, it does not work on the graphics.  In other words, when select Euros, all of the cells that have currency in them change to Euros....but the graphics remain in dollars.  I think this may be a simple problem to solve if I allow the graphic to connect to the source data and have the source data change currency.  

Here is the original post:

https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28676034.html

Help?

B.
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
Hi,

the currency should reflect the data source.

Could you send a dummy?

Regards
CERTIFIED EXPERT

Commented:
try add this into your worksheet's macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 And Target.Column = 5 Then
        Dim c As Chart
        Set c = ActiveSheet.ChartObjects(1).Chart
        Select Case Target.Value
        Case "Dollar"
            f = "$#,##0"
        Case "GBP-Pound"
            f = "[$£-809]#,##0"
        Case "Euro"
            f = "[$€-483]#,##0"
        End Select
        c.Axes(xlValue).TickLabels.NumberFormat = f
    End If
End Sub

Open in new window

CERTIFIED EXPERT

Commented:
it seems that Conditional Formatting is no longer working if I added that scripts above. So I may remove the Conditional Formatting and use scripts to set the format of the values in the worksheet instead, which this working fine for me.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 And Target.Column = 5 Then
        Dim c As Chart
        Set c = ActiveSheet.ChartObjects(1).Chart
        Select Case Target.Value
        Case "Dollar"
            f = "$#,##0"
        Case "GBP-Pound"
            f = "[$£-809]#,##0"
        Case "Euro"
            f = "[$€-483]#,##0"
        End Select
        c.Axes(xlValue).TickLabels.NumberFormat = f
        Range("I12:J15").NumberFormat = f
    End If
End Sub

Open in new window

Currency-Formatting_b.xlsm

Author

Commented:
OK..... just created this for you to look at.  You will see when you select Euros for example... it changes the format in the data/cells but not in the graphic.

B.
Changing-Currency-in-a-graphic.xlsm
CERTIFIED EXPERT

Commented:
@B,

you can try this which is based on your latest uploaded sample.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 12 And Target.Column = 8 Then
        Dim c As Chart
        Set c = ActiveSheet.ChartObjects(1).Chart
        Select Case Target.Value
        Case "Dollars"
            f = "$#,##0"
        Case "GBP-Pound"
            f = "[$£-809]#,##0"
        Case "Euros"
            f = "[$€-483]#,##0"
        End Select
        c.SeriesCollection(1).DataLabels.NumberFormat = f
        Range("H16:K16").NumberFormat = f
    End If
End Sub

Open in new window


Remember to remove the Conditional Formatting in your worksheet
Changing-Currency-in-a-graphic_b.xlsm

Author

Commented:
Ryan,

I see you are using the Macro approach.  I believe that both of the previous EE Pros who answered the first question thought that given I had multiple Worksheets in the WB, that the Macro Approach would be more difficult.  Additionally, it would need to be "triggered".  I don't think it would be a problem and probably a clean approach.  When I applied the last fix, as described on the other Solution, I approached it from the Conditional Formatting approach.  This is what I just uploaded.  So....

1.) If I were to go back and use the Macro approach, how would I scale that across multiple WSs?  Place it in a Module?  How would I identify the cells to be changed universally?

2.) Take a look at the Conditional approach I uploaded.  I'm still wondering how to insure attachment of the change to the graphic.

Thoughts?

B.
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
OK.....let me work on this........  thanks for the input and reply.  B.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.