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

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,

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

On Fri, 28 Mar 2008 22:39:00 -0700, Jeff
wrote:

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,


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

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

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

On Fri, 28 Mar 2008 22:39:00 -0700, Jeff wrote:

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,

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

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!

"Gord Dibben" wrote:

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

On Fri, 28 Mar 2008 22:39:00 -0700, Jeff wrote:

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,