Make all sheets scroll to top when I protect them...

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

I have a macro that protects all sheets (and it does a few other things at
the same time):

I would like to include some code that make all the sheets scroll to the
top. I tried adding "ws.Range("O1").Select" but that doesn't work.

Sub Protect1_Click()
For Each ws In Worksheets
ws.Range("O29:Z29").Interior.ColorIndex = 2
ws.Shapes("Date Hired").LockAspectRatio = msoFalse
ws.Shapes("Date Hired").Height = 28.5
ws.Shapes("Date Hired").Width = 112.5
ws.Shapes("Unprotect1").ZOrder msoBringToFront
ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Next
End Sub

Thanks in advance,
 
I have a macro that protects all sheets (and it does a few other things at
the same time):

I would like to include some code that make all the sheets scroll to the
top. I tried adding "ws.Range("O1").Select" but that doesn't work.

Sub Protect1_Click()
For Each ws In Worksheets
ws.Range("O29:Z29").Interior.ColorIndex = 2
ws.Shapes("Date Hired").LockAspectRatio = msoFalse
ws.Shapes("Date Hired").Height = 28.5
ws.Shapes("Date Hired").Width = 112.5
ws.Shapes("Unprotect1").ZOrder msoBringToFront
ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Next
End Sub

Thanks in advance,
Click to expand...


Try this sub:

Sub Scroll_all_sheets_to_top()
Set a = ActiveSheet
For Each ws In Worksheets
ws.Activate
ActiveWindow.ScrollIntoView 0, 0, 0, 0
Next ws
a.Activate
End Sub

Hope this helps. / Lars-Åke
 
You want O1 to be top left cell on each sheet?

Adjust Lars' code to this.

Sub Scroll_all_sheets_to_top()
Set a = ActiveSheet
For Each ws In Worksheets
ws.Activate
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 15
Next ws
a.Activate
End Sub


Gord Dibben MS Excel MVP
 
Thank you, Lars & Gord for your help with this. It actually helps me in a lot
more ways than just solving this problem, it gave me different way to look at
things. I really appreciate that!
 
Back
Top