Tab Code Retrieve a Value

[Ndel40]

I’m using tab code to zoom in on a cell when it is selected. Once the user
leaves the zoomed cell, I would like to return to the previous value of the
zoom property before it was set to 120.

For example… the user is in cell A1 and they set the zoom value to 75. They
then select cell O22 and the zoom value is automatically set to 120. When
the leave cells O22, I would like to have the value of zoom set back to 75.

I realize I could set this manually with ActiveWindow.Zoom = 75, however,
most users have different zoom preferences, so I would like it to go back to
what they had set.

My code is below...

Thanks

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim a As Integer

a = ActiveWindow.Zoom
Range("a1").Value = a


If Target.Address = "$O$22" Then
ActiveWindow.Zoom = 120

ElseIf Target.Address = "$D$9:$E$9" Then
ActiveWindow.Zoom = 120

ElseIf Target.Address = "$C$19" Then
ActiveWindow.Zoom = 120

ElseIf Target.Address = "$C$21" Then
ActiveWindow.Zoom = 120

Else
ActiveWindow.Zoom = a
End If
End Sub

 

[Tim Williams]

Not sure if it should only zoom with single-cell selections.
If this is what you want then this change should work:

If Not Intersect(Target.Cells(1), Me.Range(RNG_ZOOM)) Is Nothing Then
.....


'*****************************************************
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Const RNG_ZOOM As String = "O22,D9:E9,C19,C21"
Static a
Static bZoomed As Boolean

If Not Intersect(Target, Me.Range(RNG_ZOOM)) Is Nothing Then
If Not bZoomed Then
bZoomed = True
a = ActiveWindow.Zoom
ActiveWindow.Zoom = 120
End If
Else
If bZoomed Then
bZoomed = False
ActiveWindow.Zoom = IIf(a <> 0, a, 100)
End If
End If

End Sub
'*****************************************************

Tim

 

[Doug Glancy]

I think this will work for you. I changed a to a module-level variable.
You could also make it a static variable in the procedure. I changed your
If Then to a Select Case:

Option Explicit
Dim a As Integer
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Address
Case "$O$22", "$D$9:$E$9", "$C$19", "$C$21"
a = ActiveWindow.Zoom
ActiveWindow.Zoom = 120
Case Else
If a = 0 Then
a = ActiveWindow.Zoom
End If
ActiveWindow.Zoom = a
End Select
End Sub

hth,

Doug

 

[Ndel40]

Both of the suggestions work! It's nice to have options.

Thanks