hi, i need a vba macro to zoom in using mouse move function on an XY chart in excel. i tried this code but it doest seem to work, i keep getting error in Activesheet.shapes("zoom area")



Option Explicit


' ----------------------------------------------------------------------------------------------------------------------------------
' Declaration of global variables
Public dblStartX As Double ' selected (start) X coordinate on map / label (pixel offset from left, not longitude)
Public dblStartY As Double ' selected (start) Y coordinate on map / label (pixel offset from top, not latitude)
Public dblXScale As Double ' scale factor X coordinate to transfer worksheet position to chart position
Public dblYScale As Double ' scale factor Y coordinate to transfer worksheet position to chart position
Public blnZoomMode As Boolean ' boolean variable, true, if zooming process is running


' ----------------------------------------------------------------------------------------------------------------------------------
Private Sub LArea_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)
' ----------------------------------------------------------------------------------------------------------------------------------

If blnZoomMode Then
' execute only when zooming mode is running

' workaround to make the label visible
' (label disappears after clicking and is only shown again after mouse is moved away from label)
With LArea
.Visible = False
.Visible = True
End With

' Update position and size of the rectangle
With ActiveSheet.Shapes("Zoom Area")
.Left = dblStartX
.Top = dblStartY
.Width = LArea.Left + x - dblStartX
.Height = LArea.Top + y - dblStartY
End With

End If

End Sub


' ----------------------------------------------------------------------------------------------------------------------------------
Private Sub LArea_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)
' ----------------------------------------------------------------------------------------------------------------------------------
Const C_FONT_SIZE_STATES_MIN As Integer = 10 ' minimum font size of state data labels
Const C_FONT_SIZE_STATES_MAX As Integer = 18 ' maximum font size of state data labels
Const C_FONT_SIZE_CITIES_MIN As Integer = 9 ' minimum font size of city data labels
Const C_FONT_SIZE_CITIES_MAX As Integer = 14 ' maximum font size of city data labels


Dim dblChartAspectRatio As Double ' aspect ratio of chart, needed to adjust the zoom area correctly
Dim dblAdjustedFontSize As Double ' new font size of data labels during zoom
Dim intChartCount As Integer ' loop counter for adjustments on both charts (XY scatter and bubble chart)


' workaround to make the label visible
' (label disappears after clicking and is only shown again after mouse is moved away from label)
With LArea
.Visible = False
.Visible = True
End With

If Button = 1 Then
' left mouse button pressed: start or stop zoom mode

' toggle toom mode
blnZoomMode = Not blnZoomMode

If blnZoomMode Then
' start zoom selection

' calculate scale factors to transfer worksheet coordinates to chart coordinates
With ActiveSheet.ChartObjects(1).Chart
dblXScale = (.Axes(xlCategory).MaximumScale - .Axes(xlCategory).MinimumScale) / LArea.Width
dblYScale = (.Axes(xlValue).MaximumScale - .Axes(xlValue).MinimumScale) / LArea.Height
End With


' force 100% zoom and maximize active Excel window
If ActiveWindow.Zoom <> 100 Then ActiveWindow.Zoom = 100
Application.WindowState = xlMaximized

' add label top and left to the clicked coordinates to get the worksheet positions
dblStartX = LArea.Left + x
dblStartY = LArea.Top + y

' initialize the rectangle (position at selected coordinates, minimum size and make visible)
With ActiveSheet.Shapes("Zoom Area")
.Left = dblStartX
.Top = dblStartY
.Width = 1
.Height = 1
.Visible = True
End With
Else
' stop zoom selection

' calculate aspect ratio of plot area
With ActiveSheet.ChartObjects(1).Chart.PlotArea
If .Height > .Width Then
dblChartAspectRatio = .Height / .Width
Else
dblChartAspectRatio = .Width / .Height
End If
End With

With ActiveSheet.Shapes("Zoom Area")
' adjust current X and Y to keep the correct aspect ratio of the chart
If .Height >= .Width Then
x = dblStartX - LArea.Left + .Height * dblChartAspectRatio
Else
y = dblStartY - LArea.Top + .Width * 1 / dblChartAspectRatio
End If


' hide the zoom area rectangle
.Visible = False
End With


' adjustments of both charts (axes scales and data label font sizes)
For intChartCount = 1 To 2

' change axes scales to zoom in
With ActiveSheet.ChartObjects(intChartCount).Chart
.Axes(xlCategory).MaximumScale = .Axes(xlCategory).MinimumScale + dblXScale * x
.Axes(xlCategory).MinimumScale = .Axes(xlCategory).MinimumScale + dblXScale * (dblStartX - LArea.Left)
.Axes(xlValue).MinimumScale = .Axes(xlValue).MaximumScale - dblYScale * y
.Axes(xlValue).MaximumScale = .Axes(xlValue).MaximumScale - dblYScale * (dblStartY - LArea.Top)

' Calculate new font sizes of data labels
If intChartCount = 1 Then
' XY scatter chart: adjust state data labels
dblAdjustedFontSize = C_FONT_SIZE_STATES_MAX - _
((C_FONT_SIZE_STATES_MAX - C_FONT_SIZE_STATES_MIN) * _
((.Axes(xlCategory).MaximumScale - .Axes(xlCategory).MinimumScale) * _
(.Axes(xlValue).MaximumScale - .Axes(xlValue).MinimumScale)) / _
[myChtPlotAreaSize])
Else
' bubble chart: adjust city data labels
dblAdjustedFontSize = C_FONT_SIZE_CITIES_MAX - _
((C_FONT_SIZE_CITIES_MAX - C_FONT_SIZE_CITIES_MIN) * _
((.Axes(xlCategory).MaximumScale - .Axes(xlCategory).MinimumScale) * _
(.Axes(xlValue).MaximumScale - .Axes(xlValue).MinimumScale)) / _
[myChtPlotAreaSize])
End If

End With

' adjust font sizes of data labels
ActiveSheet.ChartObjects(intChartCount).Chart.SeriesCollection(1).DataLabels.Font.Size = dblAdjustedFontSize

Next intChartCount
End If

ElseIf Button = 2 Then
' right mouse button pressed: reset zoom

blnZoomMode = False

' restore original axes scales
For intChartCount = 1 To 2
With ActiveSheet.ChartObjects(intChartCount).Chart
.Axes(xlCategory).MinimumScale = [myChtAxesScales].Cells(1, 1)
.Axes(xlCategory).MaximumScale = [myChtAxesScales].Cells(2, 1)
.Axes(xlValue).MinimumScale = [myChtAxesScales].Cells(3, 1)
.Axes(xlValue).MaximumScale = [myChtAxesScales].Cells(4, 1)
End With
Next intChartCount

' restore data label font sizes
ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).DataLabels.Font.Size = C_FONT_SIZE_STATES_MIN
ActiveSheet.ChartObjects(2).Chart.SeriesCollection(1).DataLabels.Font.Size = C_FONT_SIZE_CITIES_MIN

' hide the zoom area rectangle
ActiveSheet.Shapes("Zoom Area").Visible = False
End If

End Sub