Excel VBA code for charts

Hi Gary,

I've contacted Peltier and he didn't solve those issues after 2003 and when he does wouldn't be willing to give the code away which is understandable.

I found a way around it though using some imagination...

It works really well for what I need here at work having to zoom in and out of small areas many times.

I set up the chart image as a background in the spreadsheet, and selecting the cells in the spreadsheet I could compare it with the current max and min scales to have them changed in the original chart and setting the image of the new chart as a background again.
The areas around your chart I simply made it white because you have chart images all over the spreadsheet.
Sounds complicated but it's very simple.
To make the selection quite refined I set up the column width as 0.5 and row height as 5, but you can go even lower. To look neater I've hidden the row and column headers.
I've got some extra code to bring it back to auto scale etc.. but just left out of the script to leave only the basic idea.
The ideal would be setting something with chart event mouse down, mouse move, mouse up but the issues related with this code is not solved and for now this is all I need.
The code which you put in the worksheet to monitor selection change looks like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Monitors only the plotting area (change it to suit your chart)
If Not Intersect(Target, Range("I9:GD186")) Is Nothing Then
'Defines limits of rows of the chart which you can change
FirstRowOfChartArea = 9
LastRowOfChartArea = 99
TotalChartRows = LastRowOfChartArea - FirstRowOfChartArea

'Defines limits of columns of the chart which you can change to suit your chart
FirstColumnOfChartArea = 9
LastColumnOfChartArea = 186
TotalChartColumns = LastColumnOfChartArea - FirstColumnOfChartArea

'Monitors selected area rows
FirstRowSelectedInChartArea = Selection.Cells(1, 1).Row
LastRowSelectedInChartArea = Selection.Rows.Count + FirstRowSelectedInChartArea - 1

'Monitors selected area columns
FirstColumnSelectedInChartArea = Selection.Cells(1, 1).Column
LastColumnSelectedInChartArea = Selection.Columns.Count + FirstColumnSelectedInChartArea - 1

ActiveSheet.ChartObjects("Chart1").Activate
'Gets values of min and max scales from the chart
MinYScale = ActiveChart.Axes(xlValue).MinimumScale
MaxYScale = ActiveChart.Axes(xlValue).MaximumScale

MinXScale = ActiveChart.Axes(xlCategory).MinimumScale
MaxXScale = ActiveChart.Axes(xlCategory).MaximumScale

'Checks in x and y directions the number of points per cell
YPointsPerDivision = (MaxYScale - MinYScale) / TotalChartRows
XPointsPerDivision = (MaxXScale - MinXScale) / TotalChartColumns

'Calculates new X min and max values
NewMaxY = MaxYScale - ((FirstRowSelectedInChartArea - FirstRowOfChartArea) * YPointsPerDivision)
NewMinY = MinYScale + ((LastRowOfChartArea - LastRowSelectedInChartArea) * YPointsPerDivision)

'Calculates new Y min and max values
NewMaxX = MaxXScale - ((LastColumnOfChartArea - LastColumnSelectedInChartArea) * XPointsPerDivision)
NewMinX = MinXScale + ((FirstColumnSelectedInChartArea - FirstColumnOfChartArea) * XPointsPerDivision)

'Sets min and max scales to the new calculated values
ActiveSheet.ChartObjects("Chart1").Activate
'X Values
ActiveChart.Axes(xlCategory).MinimumScale = Round(NewMinX, 0)
ActiveChart.Axes(xlCategory).MaximumScale = Round(NewMaxX, 0)

'Y Values
ActiveChart.Axes(xlValue).MinimumScale = Round(NewMinY, 1)
ActiveChart.Axes(xlValue).MaximumScale = Round(NewMaxY, 1)

'Saves the chart as a jpeg and puts the picture in the background
Dim ForcePic As String
ForcePic = Environ$("temp") & "\Chart1.jpg"
ActiveWorkbook.Worksheets("Sheet1").ChartObjects("LVDT").Chart.Export _
Filename:=ForcePic, FilterName:="JPG"
Sheets("Sheet1").Activate
ActiveSheet.SetBackgroundPicture ForcePic
Kill ForcePic
End If
End Sub

Hope it helps
Regards
Fabio