Freeze Panes anchor relocates in shared workbook

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

Guest

Okay so I have a report which is run every morning. Once it is complete, the
workbook is shared. At this point, on the computer of the person running the
report:

1) on Sheet1 the panes are frozen on cell A2
2) on Sheet2 the "zoom" is set to 75%

BUT, when you open the file from another computer, the following occurs:

1) on Sheet1, the panes are now frozen on cell A1 (i.e. above the column
headings, which is pretty useless)
2) on Sheet2, the "zoom" has been re-set to 100%

It never used to do this. Any thoughts from anyone?

Thanks!

Pete
 
You could use a workbook Open MAcro to set the scene

Right_Click the Excel icon on the left of the File Menu and click View code.
This opens the VB Editor. Copy the code into the Workbook code and save the
file.

As the code works when the book is opened every one should have the right
settings.

Sub Workbook_Open()
Application.ScreenUpdating = False
Worksheets(1).Select
Range("B2").Select
ActiveWindow.FreezePanes = True
Sheets("Sheet2").Select
ActiveWindow.Zoom = 75
Sheets("sheet1").Select
Application.ScreenUpdating = True
End Sub


Regards
Peter
 
Thanks Peter! I did exactly this, but for some reason the panes are
sometimes freezing in the middle of the page rather than on cell A2. Even
when I select cell A1 first. I took out the screenupdating lines, which
fixes this problem, but then I have to watch it all play out (on 32 tabs).
Still, it works :)
 
Pete

The screenupdating properties come in pairs, first to stop the screen
jumping while the code does its work and then to restore the screen at the
end.

I'd restore both lines to prevent screen flicker.

Sorry for the delay I replied to you before I was going to bed.

Regards
Peter
 
Pete

I assume from your reply that you want the 32 sheets at 75 zoom and each
sheet frozen at a2. The revised code will do this.

Sub allsheets()
Dim wks As Worksheets, nw As Integer
Dim i As Integer
Application.ScreenUpdating = False
Worksheets(1).Select
Range("a2").Select
ActiveWindow.FreezePanes = True
For i = 2 To Sheets.Count
' Set wks = Sheets(i)
' With wks(i)
Worksheets(i).Select
Cells(2, 1).Select
ActiveWindow.FreezePanes = True
ActiveWindow.Zoom = 75
'End With
Next
Application.ScreenUpdating = True

Worksheets(1).Select
End Sub

regards
Peter
 
Back
Top