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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeff - 29 Mar 2008 06:39 GMT

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,

Reply to this Message

Lars-Åke Aspelin - 29 Mar 2008 09:21 GMT

>I have a macro that protects all sheets (and it does a few other things at
>the same time):
[quoted text clipped - 14 lines]
>
>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

Reply to this Message

Gord Dibben - 29 Mar 2008 17:24 GMT

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

>I have a macro that protects all sheets (and it does a few other things at
>the same time):
[quoted text clipped - 14 lines]
>
>Thanks in advance,

Reply to this Message

Jeff - 29 Mar 2008 23:26 GMT

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!

> You want O1 to be top left cell on each sheet?
>
[quoted text clipped - 30 lines]
> >
> >Thanks in advance,

Reply to this Message