Changing Currency in a Graphic (Axis)

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:

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

Help?

B.
Bright01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

the currency should reflect the data source.

Could you send a dummy?

Regards
0
Ryan ChongCommented:
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

0
Ryan ChongCommented:
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
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Bright01Author 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
0
Ryan ChongCommented:
@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
0
Bright01Author 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.
0
Ryan ChongCommented:
>>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?
first thought is... is that the dropdown list and the cells always in the same position? if no, that some customization is needed here

>>2.) Take a look at the Conditional approach I uploaded.  I'm still wondering how to insure attachment of the change to the graphic.
I think first of all, to enable us to change the label format in a chart dynamically is impossible without the involvement of macro.

And then as I explained earlier, it seems that when I'm using macro, the conditional formatting for the cells is no longer working. I'm not yet doing a deeper diagnosis why it behavious like this... so for a quick solution, that's why I'm going to format those cells using macro.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
OK.....let me work on this........  thanks for the input and reply.  B.
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.