How to Format Chart Axis Based on Cell Format

[namingway]

Hey there,

Regular lurker, first time poster.

In Excel 2007, I need a chart's vertical axis to change based on the reference cell format changing. Basically, I have a "dashboard" that will allow a user to select whether they want to view units or dollars in a table.

The table that the chart is referencing grabs data from another table based on a data validation list, and I've conditionally formatted the table's cells to be currency if dollars is selected, and numbers if units is selected. That is awesome, but I cannot figure out how to make the axis reflect the change. So far it only shows numbers despite the cell format changing.

Any ideas appreciated, thanks!

 

[vaskov17]

Right click on the axis and select "Format Axis" from the menu, then under "Number" check the "Linked to Source" box

 

[Misca]

Welcome to the message board!

Try something like

ActiveChart.Axes(xlValue).TickLabels.NumberFormat = Range("A1").NumberFormat

 

[namingway]

I just gave that another go to check. It defaults to number. I clicked on "general" on number, then clicked on link to source, and then it goes back to "number." I tried clicking on currency and link to source, same thing happens.

O now I went to the cells themselves and conditional formatting is changing the format, but not the cell properties. I just highlighted one of the table cells when the dollar option is selected, and it is showing as number not currency even though the data is displaying as currency.

I'm using a formula to format the cells in conditional formatting:
=IF($N$5="dollars",TRUE,FALSE)

which is applied to the table range. And set the format to be currency. Which again it looks like but the actual cell properties aren't changing.

Should I try using something else?

 

[namingway]

Hi Misca thanks for sharing. I am pretty light on code (only using one currently, a little macro to hide some rows via a checkbox).

Can you let me know how to add that code? Do I need to add a different macro?

 

[Misca]

If there's only one chart on your sheet a simple

Sub FormatChartAxis()

    ActiveSheet.ChartObjects(1).Chart.Axes(xlValue).TickLabels.NumberFormat = Range("A1").NumberFormat

End Sub

should be enough. You should have that piece of code pasted in a module in your workbook (if there's a module already you can use the existing one: Just paste the code after all the other macros / functions in that module) and use a button or whatever to fire the macro.

If there's no modules in your project yet, you're going to have to insert one (Inset/Module in your VB Editor).

If there's more than one chart on your sheet, find out the number of that chart or record a macro where you select that chat (=you'll get the name) and when you spot the ActiveChart in your recorded code, you'll know where to paste the rest of the code.

Oh, and don't forget to replace the Range("A1").NumberFormat with the actual cell range.

 

[namingway]

ahh ok. i was hoping the change could be done automatically upon the data validation selection, silly me. i'll just have to add a macro to that then.

thanks for your help!