Button to Zoom in a Graph in Excel – [Method 2]
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 whihc 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 involved in this process – [Method 2]
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 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.
In the above steps, the sequence of step and 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.
Download the Sample
I have created one sample Excel file with such Zoom-in and Zoom-out feature. You can download it and play around.
Explanation of VBA Code used to build this
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
Download FREE Copy to Play around
Download a FREE Copy of this Method and Play around. Happy excel macro learning
My name is Vishwamitra Mishra. Friends Call me Vishwa. This blog is authored by me. I am an Excel Geek. Well, this blog talks a lot about my passion in Excels & Macros so I’ll not talk about it :) I am very much passionate about traveling & quite recently discovered that I am a good photographer too..:P
AND GET A FREE!
E-BOOK FOR EXCEL VBA BEGINNERS
DON'T MISS ANY NEW ARTICLE !
Get your FREE! E-Book & Have Every New Article Delivered Straight To Your Email-Box