Macro 29: Zooming In and Out of a Worksheet with Double-Click
Some spreadsheets are huge. Sometimes, we are forced to shrink the font size down so that we can
see a decent portion of the spreadsheet on the screen. If you find that you are constantly zooming in
and out of a spreadsheet, alternating between scanning large sections of data and reading specific
cells, here is a handy macro that will auto-zoom on double-click.
How it works
With this macro in place, you can double-click on a cell in the spreadsheet to zoom in 200 percent.
Double-click again and Excel zooms back to 100 percent. Obviously, you can change the values and
complexity in the code to fit your needs.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
‘Check current Zoom state
‘Zoom to 100% if to at 100
‘Zoom 200% if currently at 100
If ActiveWindow.Zoom <> 100 Then
ActiveWindow.Zoom = 100
Else
ActiveWindow.Zoom = 200
End If
End Sub
Application.SendKeys (“{ESC}”)
This statement mimics you pressing Esc on your keyboard.
How to use it
To implement this macro, you need to copy and paste it into the Worksheet_BeforeDouble
Click event code window. Placing the macro there allows it to run each time you double-click on
the sheet.
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. In the Project window, find your project/workbook name and click the plus sign next to it in
order to see all the sheets.
3. Click on the sheet from which you want to trigger the code.
4. Select the BeforeDoubleClick event from the Event drop-down list (see Figure 3-1).
5. Type or paste the code in the newly created module.
Figure 3-1: Type or paste your code into the Worksheet_BeforeDoubleClick event code window.
