chart sheet value (Y) axis scale data report - an example

[DataFreakFromUtah]

Hello. No question here, just a procedure for the archive.

Returns value (Y) axis scale data (the max and min scales and major
and minor units) for all charts sheets in active workbook.


Sub ChartSheetScaleReport()
'Return all scales for all chart sheets in active workbook
On Error Resume Next

If ActiveWorkbook.Charts.Count = 0 Then
MyVar = MsgBox("There are no chart sheets in this workbook")
If MyVar = vbOK Then _
Exit Sub
End If

ActiveWorkbook.Worksheets.Add.Name = "ChartSheetScales"
Sheets("ChartSheetScales").Select
Range("A1").Select
ActiveCell.Value = "Workbook Name"
ActiveCell.Offset(0, 1).Value = "Chart Sheet Name"
ActiveCell.Offset(0, 2).Value = "Min Scale"
ActiveCell.Offset(0, 3).Value = "Max Scale"
ActiveCell.Offset(0, 4).Value = "Major Unit"
ActiveCell.Offset(0, 5).Value = "Minor Unit"

Range("A2").Select

For iChart = 1 To ActiveWorkbook.Charts.Count
ActiveCell.Offset(iChart - 1, 0) = ActiveWorkbook.Name
ActiveCell.Offset(iChart - 1, 1) = Charts(iChart).Name
ActiveCell.Offset(iChart - 1, 2) =
Charts(iChart).Axes(xlValue).MinimumScale
ActiveCell.Offset(iChart - 1, 3) =
Charts(iChart).Axes(xlValue).MaximumScale
ActiveCell.Offset(iChart - 1, 4) =
Charts(iChart).Axes(xlValue).MajorUnit
ActiveCell.Offset(iChart - 1, 5) =
Charts(iChart).Axes(xlValue).MinorUnit

Next iChart

Sheets("ChartSheetScales").Select
Columns("A:F").EntireColumn.AutoFit

End Sub



Search criteria:
get chart scales return chart scales get chart sheet scales return
chart sheet scales extract chart
sheet scales return chart maximum scale return chart minimum scale
return major units return minor units
extract chart sheet scale data extract chart sheet scale values

 

[DataFreakFromUtah]

Hello! no question here just a correction to previous post.

Dim MyVar As String
Dim iChart As Integer
needs to be added to ChartSheetScaleReport()

Also below is a procedure to load chart sheet scale data back into the
active workbook:

Sub ChartSheetScalesMassLoad()

'Use this procedure to mass load chart sheet scale data back into
'your active workbook containing the chart sheets that are referenced
in
' a range named DATA.
Dim MyVar As String
Dim iChart As Integer
Dim cell As Range
Dim Found As Boolean
On Error Resume Next

If ActiveWorkbook.Charts.Count = 0 Then
MyVar = MsgBox("There are no chart sheets in this workbook")
If MyVar = vbOK Then _
Exit Sub
End If

For iChart = 1 To ActiveWorkbook.Charts.Count

For Each cell In [DATA] 'can use this format as well:
[A1:A43]

'Workbook Name is the column where the range is named DATA
'Chart Name is the 2nd column to the right of Workbook Name
'Min Scale is the 3rd column to right of Workbook Name
'Max Scale is the 4th column to the right of Workbook Name
'Major Unit is the 5th column to the right of Workbook Name
'Minor Unit is the 6th column to the right of Workbook Name

If cell.Value = ActiveWorkbook.Name And cell.Offset(0,
1).Value = Charts(iChart).Name Then

Charts(iChart).Axes(xlValue).MinimumScale =
cell.Offset(0, 2)
Charts(iChart).Axes(xlValue).MaximumScale =
cell.Offset(0, 3)
Charts(iChart).Axes(xlValue).MajorUnit =
cell.Offset(0, 4)
Charts(iChart).Axes(xlValue).MinorUnit =
cell.Offset(0, 5)
Found = False
End If
Next cell

Next iChart


End Sub


The scale data resides in a worksheet is in this format:

WKBookName Chart Sheet Name Min Scale Max Scale Major Unit Minor Unit
FileNameOne.xls Chart1 0.5 21 2 1
FileNameOne.xls Chart2 1 22 3 1
FileNameOne.xls MiddleName 2 23 4 1
FileNameOne.xls LastOneHere 3 24 5 1
FileNameTwo.xls Chart1 0 2 15 6
FileNameTwo.xls Chart2 0 3 12 8
FileNameTwo.xls MiddleName 0 4 20 14
FileNameTwo.xls LastOneHere 0 5 13 10
FileNameThree.xls Chart1 1 22 3 1
FileNameThree.xls Chart2 2 23 4 1
FileNameThree.xls MiddleName 3 24 5 1
FileNameThree.xls LastOneHere 12 10 15 6

Where the column WKBookName is the range named DATA.