Button to Zoom in a Graph in Excel – [Method 2]

.

Hello Friends,

How are you doing? I am back with my next article about “how to make a button to zoom-in and zoom-out a graph in Excel VBA”. this is the second method I am going to explain you. In my previous article, I had explained about the very basic method.

There were 4 limitations in the previous method which I explained it. In this article I am going to address all the limitations mentioned in the previous method.

This method is going to be an illusion created for zoom-in and zoom-out 😉

Steps to create Zoom-in Zoom-out button for Graph

1. Taken input from the user about the width or Height
2. Resize the graph with that size (DO NOT SHOW THIS TO USER HAPPENING… by using below VBA statement)


Application.ScreenUpdate = False

3. Now save this large graph as an image in the temp folder.
4. Create a UserForm and add a placeholder for an image
5. Make the UserForm hegiht and width as “auto” – So that form size get adjusted according to the size of the image
6. While loading or showing the UserForm, embed the image which you have saved it in the temp folder.
7. Now on closing the userform having graph image, delete the image which you saved it in the temp folder and release the memory.

Important

In the above steps, the sequence of step 2 and 3 is very important. If you are resizing the image after saving it, then you will see only a big picture of the same size graph. But here the purpose is to zoom-in the graph and see every small details in it which might have got hidden because of this size of the graph.

Explanation of VBA Code used to build this

Read carefully all the comments which I have provided in the below codes. They are easy to understand like what each and every statement of VBA code is doing? What is the use of them. If you still have any doubt or question or suggestion.. feel free to let me know by commenting your question in the comment section below:


Public tempFileName
Sub Zoom_Chart()

Dim i As Integer
Dim strChartName As String
Dim dZoomInWidth As Double
Dim dZoomInHeight As Double
Dim dOutWidth As Double
Dim dOutHeight As Double
Dim rngZoom As Range
Dim rngChart As Range
         
        dZoomInWidth = ActiveSheet.[rngZoomWidth].Value '<-THIS CAN BE CHANGED
        dZoomInHeight = ActiveSheet.[rngZoomHeight].Value '<-THIS CAN BE CHANGED
        dOutWidth = 1 / dZoomInWidth 'zooms back to original size
        dOutHeight = 1 / dZoomInHeight
    '-----------------------------------------------------------------------------------
    'Set the button/shape name based on the shape that was clicked by the user
    strChartName = Application.Caller
    tempFileName = VBA.Environ$("Temp") & "\zoomGraph.gif"
    'Set range of zoom button for intersection check
    Set rngZoom = Range(ActiveSheet.Shapes(strChartName).TopLeftCell.Address)
    With ActiveSheet
            Set rngChart = Range(.Shapes(strChartName).TopLeftCell.Address)
                If ActiveSheet.Shapes(strChartName).Type = msoChart Then
                With ActiveSheet.Shapes(strChartName)
                        Application.ScreenUpdating = False
                        .ZOrder msoBringToFront
                        dashboard.Unprotect password:="Vishwa@123"
                        .ScaleWidth dZoomInWidth, msoFalse, msoScaleFromTopLeft
                        .ScaleHeight dZoomInHeight, msoFalse, msoScaleFromTopLeft
                        .Chart.Export Filename:=tempFileName, FilterName:="GIF"
                        .ScaleWidth dOutWidth, msoFalse, msoScaleFromTopLeft
                        .ScaleHeight dOutHeight, msoFalse, msoScaleFromTopLeft
                        dashboard.Protect password:="Vishwa@123"
                        zoomForm.Show
                        ActiveSheet.Shapes(strChartName).ZOrder msoBringToFront
                        'Kill the temp file now
                        Kill tempFileName
                End With
                End If
    End With
End Sub

How does it look after creating this

On clicking on graph this is how you see graph based on the % provided about height and width for the Zoom.

Graph-Zoom-in-Zoom-Out-button

Graph-Zoom-in-Zoom-Out-button

Download FREE Copy to Play around

I have created one sample Excel file with such Zoom-in and Zoom-out feature. You can download it and play around.. Happy excel macro learning 🙂

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

1 Comment

  1. Frank

    Hey Vishwa,
    Thanks for posting this, and for all the other great resources you’ve posted.
    I’m wondering if you have a method for adjusting the range of a chart to match new data. So, if I have a histogram produced by QI Macros saved in a template where I’ll enter different data for different projects, I’d like to have that histogram’s X and Y ranges adjust to fit the new data. I haven’t been able to do this and have all of the references within the chart window update as well (lines representing mean, LSL, USL, etc.). The only reliable method I’ve come up with is to rerun the macros that create the charts every time the data changes. That’s fine for me, but I’d like to have a template to be used by people who don’t necessarily know how to run those macros.

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest