Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I make the scroll bar show the row number? | Excel Discussion (Misc queries) | |||
no scroll bars or tab sheets | Excel Worksheet Functions | |||
have sheets 2 and 3 scroll the same when scrolling sheet 1? | Excel Discussion (Misc queries) | |||
Can you make all sheets scroll when scrolling one sheet | Excel Worksheet Functions | |||
How do I make a row or information, follow as you scroll down..? | Excel Worksheet Functions |