VBA | With opening/activating sheet > edit viewing screen/zoom

[TimvMechelen]

Hi all,

When I activate a sheet I want excel to do the following process:

Is cell "Z1" in the range of my screen?

Yes:
Do nothing

No:

Range("A1:Z1").Select
ActiveWindow.Zoom = True

I want this because my file has to open on different screens/screen resolutions and everything has to be visible.


I hope somebody can help me.

 

[BarryL]

Put this in the worksheet object in VBA

Private Sub Worksheet_Activate()
If ActiveWindow.Zoom > 100 Then ActiveWindow.Zoom = 100
End Sub

 

[TimvMechelen]

This code doesn't work for me. What I need, is:
When cell Z1 isn't in my screen/view THEN Range("A1:Z1").Select
ActiveWindow.Zoom = True

 

[tonyyy]

You might consider the following...

Private Sub Worksheet_Activate()
If Intersect(ActiveWindow.VisibleRange, Range("Z1")) Is Nothing Then
    Range("A1:Z1").Select
    ActiveWindow.Zoom = True
End If
End Sub

Cheers,

tonyyy

 

[TimvMechelen]

You might consider the following...

Private Sub Worksheet_Activate()
If Intersect(ActiveWindow.VisibleRange, Range("Z1")) Is Nothing Then
    Range("A1:Z1").Select
    ActiveWindow.Zoom = True
End If
End Sub

This code always does

Range("A1:Z1").Select
ActiveWindow.Zoom = True

That code only has to be activated when I activate the sheet and Cell Z1 is not visable in my screen

Thanks for your time

 

[TimvMechelen]

I fixed the problem with this code:

Private Sub Worksheet_Activate()

If CellIsInVisibleRange(ActiveSheet.Range("Z2")) Then

'Nothing
Else
    Range("A2:Z2").Select
    ActiveWindow.Zoom = True
    Range("D3").Select
End If

End Sub

Function CellIsInVisibleRange(cell As Range)

CellIsInVisibleRange = Not Intersect(ActiveWindow.VisibleRange, cell) Is Nothing

End Function