Chart Number Decimal Places

[John]

Is there a way to highlight the axis on a chart, then use the
increase/decrease decimal buttons like I use to be able to in Excel 2003 and
earlier? The buttons don't seem to work in Excel 2007. The only way I've
been able to change it is a multi-click and type routine, which is sloow.
Thanks.

John

 

[Bernard Liengme]

No. And this seems to be a bug since (a) the tools are not greyed out, and
(b) one can do lots of chart font formatting with tools on the Ribbon
best wishes

 

[John]

Bernard & Shane,

Thanks for the help, folks. The idea of linking to the source is on, but
it's also what's causing me the trouble. My spreadsheet entrees contain
lotttts of decimal places, since I'm looking at numbers that differ only very
slightly from one another; I don't need to see this precision in the charts
for most views, but some I do, hence the desire to bounce them up or down
using the buttons.

Thanks again for your help.

John

 

[EricG]

If your charts are on worksheets, then add a spin button to do the same
thing. You could have one spin button per chart, or name your charts and
have a drop-down that lets you select the chart you want to operate on.

(I did this with Excel 2003)

'
' Make sure the SpinButton limits are set
' to reasonable minimum (0) and maximum values!
'
Private Sub SpinButton1_Change()
Dim i As Integer
Dim nDec As Integer
Dim decStr As String
'
nDec = Me.SpinButton1.Value
If (nDec < 0) Then nDec = 0
decStr = "0."
For i = 1 To nDec
decStr = decStr & "0"
Next i
ActiveSheet.ChartObjects(1).Select
ActiveChart.Axes(xlValue).TickLabels.NumberFormat = decStr
End Sub

 

[EricG]

I also added a command button which gathers the names of all charts on the
sheet and creates a named range with them. Then I added data validation in
one cell that is based on that list. Finally, I modified the spin button
code to reference whichever chart is selected in the cell with the data
validation. It works pretty well!

One thing I forgot to do - when you select a new chart in the data
validation cell, it should set the current value of the spinbutton to the
decimal format for that chart.


'
' This button routine gathers the names of all chartobjects on a worksheet
' and puts them in a named range list on the sheet, which feeds a data
' validation cell on the sheet.
'
Private Sub CommandButton1_Click()
Dim i As Integer
Dim cO As ChartObject
Dim tStr As String
'
i = 0
For Each cO In ActiveSheet.ChartObjects
i = i + 1
ActiveSheet.Cells(i + 1, 9) = cO.Name
Next
ActiveSheet.Range("I2:I" & i).Select
ActiveWorkbook.Names.Add Name:="MyCharts" & ActiveSheet.Name,
RefersToR1C1:= _
"='" & ActiveSheet.Name & "'!R2C9:R" & i + 1 & "C9"
End Sub

'
' Make sure the SpinButton limits are set
' to reasonable minimum (0) and maximum values!
'
Private Sub SpinButton1_Change()
Dim i As Integer
Dim nDec As Integer
Dim decStr As String
'
nDec = Me.SpinButton1.Value
If (nDec < 0) Then nDec = 0
decStr = "0."
For i = 1 To nDec
decStr = decStr & "0"
Next i
'
' Note the use of the cell with the data validation as a way to select
' which chart to change the decimals on
'
ActiveSheet.ChartObjects(ActiveSheet.Cells(9, 6).Text).Select
ActiveChart.Axes(xlValue).TickLabels.NumberFormat = decStr
End Sub

 

[EricG]

....which is easily accomplished by adding this code to the worksheet:

'
' If the data validation cell changed, reset the
' spinbutton value to the number of decimal places
' in the selected chart.
'
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F9")) Is Nothing Then
On Error Resume Next
ActiveSheet.ChartObjects(ActiveSheet.Cells(9, 6).Text).Select
decStr = ActiveChart.Axes(xlValue).TickLabels.NumberFormat
Me.SpinButton1.Value = Len(decStr) - 2 ' ignore the "0." part
On Error GoTo 0
End If
End Sub