1

Current Macro:

Private Sub Worksheet_SelectionChange

If Target.Address = "$C$11" Then
    ActiveWindow.Zoom = 120
Else
    ActiveWindow.Zoom = 55
End If

End Sub

Desired Macro:

Click on cell C11, zoom in to 120, else, zoom should be whatever the sheet is set to. I use 55, but somebody else might use 75, and I don't want my macro to stop that.

Ideas?

Teamothy
  • 2,000
  • 3
  • 16
  • 26
goatamous
  • 13
  • 3
  • Would using another cell for the zoom value of 55 or 75 work for you? – Martin Noreke Aug 14 '15 at 16:21
  • Unfortunately no because there are various people that use this one workbook. It's almost as if I want it to be a loop Working at whatever zoom i want, click on cell C11, change to 120 zoom, then click anywhere else and have it revert back to the zoom level I originally had it set at. It might not be possible. – goatamous Aug 14 '15 at 16:30
  • I'm not fully up to speed on macros, but you should be able to store the prior zoom value globally and retrieve it afterwards. See this SO question for global variables (http://stackoverflow.com/questions/2722146/how-do-i-declare-a-global-variable-in-vba). You will just need some logic for when to use the stored value, when to update it, etc. – Martin Noreke Aug 14 '15 at 16:36

1 Answers1

0

You can try this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Static zoomLevel As Integer

        'Ensure zoomLevel is set
        If zoomLevel = 0 Then
                zoomLevel = ActiveWindow.Zoom
        End If

        'If cell C11 is selected, zoom on
        If Target.Address = "$C$11" Then
                zoomLevel = ActiveWindow.Zoom
                ActiveWindow.Zoom = 120
        Else 'If any other cell is selected zoom to the user's zoomLevel
                ActiveWindow.Zoom = zoomLevel
        End If
End Sub

Using Static allows the variable to maintain its value after the execution of the subroutine ends.

Soulfire
  • 4,218
  • 23
  • 33
  • Thanks for this - I think it's going to be the best possible solution. It will never be perfect since you have to click on C11 to set the new zoomLevel, but it's definitely better than anything I can come up with. Much appreciated! – goatamous Aug 14 '15 at 18:07
  • If you don't mind clicking the green checkmark to accept this as an answer I would appreciate it! – Soulfire Aug 14 '15 at 18:54