Change Chart Axis Number Format based on a cell Value

[Dokat]

Ss it possible to write a code where number formatting in Chart Axis updates based on another cell value?

For Ex: if D1 equals 1, then chart axis number format is '$0.0,,"
or if D1 value is 2 Axis format changes to 0.0%?

 

[Hui]

You can use Custom Number formats with chart axis like
[<1]0.0%;[>=1]0.00;;0
this reads:
If the number is less than 1, display as percentage
If the number is >= 1, display as a number with 2 decimals
If the number is negative don't display
If the number is zero display as 0

For more details have a read of
https://chandoo.org/wp/2011/08/19/selective-chart-axis-formating/
https://chandoo.org/wp/2011/08/22/custom-chart-axis-formating-part-2/

 

[Dokat]

I tied Custom Number format in the Chart Axis but it didnt work.

Thanks

 

[Hui]

Dokat

It does work, as my posts above and the picture below show



This uses a Custom Format of [<=1]0.0%;[>1]$0.00;0

Can you post a sample file, so we can assist you with real data!

 

[Dokat]

Sorry i unchecked the linked to source button and it worked. Thank you for your help.

 

[p45cal]

Do you mean something like this in the sheet's code-module:

Private Sub Worksheet_Change(ByVal Target As Range)
  With ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue).TickLabels
    Select Case Range("D1").Value
      Case 1
        .NumberFormat = "$0.0,,"
      Case 2
        .NumberFormat = "0.0%"
    End Select
  End With
End Sub

?

Edit post posting: Oh, obviously not.

 

[Dokat]

Yes, i modified the code little bit change the reference sheet and cell and getting Compile Error. Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
  With ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue).TickLabels
    Select Case Worksheets.("Source Data")Range("BC1").Value
      Case 1
        .NumberFormat = "$0.0,,"
      Case 2
        .NumberFormat = "0.0%"
    End Select
  End With
End Sub

 

[p45cal]

change:
Select Case Worksheets.("Source Data")Range("BC1").Value
to:
Select Case Worksheets("Source Data").Range("BC1").Value
?