How do I stop Macro from affect other Workbooks

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a 'simple' macro in Excel2003 that will Zoom everysheet
to 128% (to allow for different user screen resolutions). I have a macro
that works but it also does any other (non-minimised) spreadsheet that is
open.

Can anyone tell me what I need to add to restrict it to the current
workbook. (NB the user is free to rename the workbook so I can not hard
program the file name in).

Sub Big()
ActiveWindow.WindowState = xlMinimized
Dim I As Integer
For I = 1 To Sheets.Count
Sheets(I).Select
ActiveWindow.Zoom = True
ActiveWindow.Zoom = 128
Next
ActiveWindow.WindowState = xlMaximized
End Sub

Many thanks
 
Possibly:

Sub Big()
Application.ScreenUpdating = False
Dim I As Integer
For I = 1 To ActiveWorkbook.Sheets.Count
Sheets(I).Select
ActiveWindow.Zoom = True
ActiveWindow.Zoom = 128
Next
Application.ScreenUpdating = True
End Sub
 
I discovered the following additions that use the value of the current
workbook name which works even better:

Sub Big()
Dim strCurrent As String
strCurrent = ActiveWindow.Caption
Application.Windows(strCurrent).WindowState = xlMinimized
Dim I As Integer
For I = 1 To ActiveWorkbook.Sheets.Count
Sheets(I).Select
Application.Windows(strCurrent).Zoom = True
Application.Windows(strCurrent).Zoom = 128
Next
End Sub

Thanks for your help
 
Back
Top