Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart sheet value (Y) axis scale data report - an example
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart sheet value (Y) axis scale data report - an example
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to force chart axis to same scale | Excel Discussion (Misc queries) | |||
How to draw chart: log scale on X axis, natural scale on y axis? | Charts and Charting in Excel | |||
Time scale axis should be available in PivotChart Report. | Charts and Charting in Excel | |||
Time scale axis should be available in PivotChart Report. | Charts and Charting in Excel | |||
How do I scale the axis of a chart from external data so that dis. | Charts and Charting in Excel |