Zooming on Charts with VBA

    I have been working through Rob Boveys book on excel development and modified the examples of dynamic charting to attempt to allow the user to zoom into a chart with a rectangular shape defining the area.


    I have a mouse down action capturing the x and y co-ordinates and use the mousemove to show the expanding rectangular area, I then have a mouse up action to capture the last x and y coordinates and change the x-axis limits, imitating a zoom feature.


    The problem is using mousemove i cannot then use the mouseup to capture the last position and reassign the axis. Has anyone else experienced something similar?


    I have attached the semi-working file and currently have the 'mousemove' commented out to make it workforum.ozgrid.com/index.php?attachment/33300/.


    If anyone has any clues as to why the interaction will not work i would appreciate the feedback.

    Re: Zooming on Charts with VBA


    I took a quick look at this and found that on my system (Excel 2003) it was not working at all. Kept reporting:

    Run-time error '1004'

    Method 'Cells' of object '_Global' failed

    This was being masked in mchtChart_MouseMove by On Error Resume Next. I went through and qualified the various Cells references that were causing this with Worksheets("Coordinates"). That seemed to make it functional again. However, I am now not sure if the behaviour I am seeing is expected or not (since its only partially finished).

    Returning my hack at it for you to try

    Re: Zooming on Charts with VBA


    Hi Rob,


    Thanks for the feedback, i see your point with the 'on error resume next' always a dangerous option!


    The changes made don't seem to resolve the problem. When the rectangle is drawn to show the required zoom area this prevents the 'mchtChart_MouseUp' in the CChart class operating. I checked this with a breakpoint in the sub and it never runs. However if you comment out the mousemove portion to modify the rectangle when the mouse is pressed it does fire the 'mchtChart_MouseUp' action and the zoom occurs.

    Re: Zooming on Charts with VBA


    Ok this is kind of strange, because I think it works for me. To make sure it wasn't something I had accidentally fixed in the version on my PC I just downloaded the file I uploaded in my post above. Made one change:

    Code
    Private Sub mchtChart_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal X As Long, ByVal Y As Long)
    [COLOR=red]Beep[/COLOR]
    With mchtChart
        .Shapes("Rectangle 126").Visible = msoFalse
        .Axes(xlCategory).MaximumScale = Round(.Axes(xlCategory).MaximumScale + (((WorksheetFunction.Max(X, homeX) * Worksheets("Coordinates").Cells(22, 15)) - Worksheets("Coordinates").Cells(4, 15)) / (Worksheets("Coordinates").Cells(23, 15) / 100)) * Worksheets("Coordinates").Cells(24, 17), 0)
        .Axes(xlCategory).MinimumScale = Round(.Axes(xlCategory).MinimumScale + (((WorksheetFunction.Min(X, homeX) * Worksheets("Coordinates").Cells(22, 15)) - Worksheets("Coordinates").Cells(4, 15)) / (Worksheets("Coordinates").Cells(23, 15) / 100)) * Worksheets("Coordinates").Cells(24, 17), 0)
        DoEvents
    End With
End Sub



    And I get a beep on MouseUp every time with or without the code in MouseMove commented out.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login