You are here:

Excel/VBA workbook syntax for zooming - Excel 2007

Advertisement


Question
Hi Tom,

I am a newbie to VBA so please bear with me.

I have this VBA code that works exactly as I wish when input as worksheet VBA within Excel 2007 i.e. when a cell in column A is highlighted the zoom increases to 120 and when a cell other than column A is highlighted, the zoom reverts to 75.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Target.Cells.Count > 1 Then Exit Sub
   If Target.Column = 1 Then
       ActiveWindow.Zoom = 120
   Else
       ActiveWindow.Zoom = 75
   End If

End Sub


I wanted to apply this code to the entire workbook so I placed it in the 'ThisWorkbook' area and changed the syntax as follows:


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   If Target.Cells.Count > 1 Then Exit Sub
   Application.EnableEvents = False
   If Target.Column = 1 Then
       ActiveWindow.Zoom = 120
   Else
       ActiveWindow.Zoom = 75
   End If
   Application.EnableEvents = True
End Sub


However, I am not getting the desired result.

In the second instance, the cells in column A do not 'automatically' zoom to 120 when highlighted.  They only zoom when I select or enter a value in column A.  After that the  zoom stays at 120 and does not revert back to 75.

Please help.

Thanks.

Answer
Jake,

You are in the correct module (thisworkbook) but using the wrong workbook level event.  The equivalent event is the SheetSelectionChange Event:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
   Application.EnableEvents = False
   If Target.Column = 1 Then
       ActiveWindow.Zoom = 120
   Else
       ActiveWindow.Zoom = 75
   End If
   Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.