How can I get a macro in Excel to set the Zoom to "Selection"?

[Guest]

I have created a Workbook_Open macro that selects a range and zoom, but the
macro only records the setting on the PC that it was created on, i.e 100%.
Can this 100 be changed within the macro to make use of the Selection option
in zoom so that it will work on other PC's with different screen
sizes/resolutions?

 

[Paul B]

Garry, here is one way

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

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Don Guillett]

This is one I did for a customer where one used 800x600 and another used
1028. It involved using the same defined name but with different selected
areas for each worksheet. Goes in the ThisWorkbook module

Private Sub Workbook_Open()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim tstRange As Range
Dim curWks As Worksheet
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
With wks
If wks.Name <> "xxx" And wks.Name <> "yyy" Then
Set tstRange = Nothing
On Error Resume Next
Set tstRange = .Range("myzoom")
On Error GoTo 0
If tstRange Is Nothing Then
'do nothing
Else
.Select
tstRange.Select
ActiveWindow.Zoom = True
.Range("a1").Select
End If
End If
End With

Next wks
Sheets("zzzz").Select
Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

End Sub