Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / November 2005

Tip: Looking for answers? Try searching our database.

Can you make all sheets scroll when scrolling one sheet

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
billy boy - 23 Nov 2005 16:47 GMT
Is it possible to have worksheets scroll at the same time? For example when
you scroll down to line 100 on sheet 1  and then you go to sheet 2 or 3 or 4
and that sheet will be on line 100 also?
Bernie Deitrick - 23 Nov 2005 18:56 GMT
Billy,

Sure, it's possible - easy, even.  Copy the first sub below (an Excel event), and paste it into the
codemodule of the Thisworkbook object.  Then copy the second sub (blantantly copied from John
Walkenbach's excellent website: http://j-walk.com/ss/excel/tips/tip75.htm) into a regular
codemodule.

For extra instructions and help working with event code, see
http://www.mvps.org/dmcritchie/excel/event.htm

HTH,
Bernie
MS Excel MVP

Private Sub Workbook_SheetSelectionChange( _
  ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
SynchSheets
Application.EnableEvents = True
End Sub

Sub SynchSheets()
'   Duplicates the active sheet's active cell upperleft cell
'   Across all worksheets
   If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
   Dim UserSheet As Worksheet, sht As Worksheet
   Dim TopRow As Long, LeftCol As Integer
   Dim UserSel As String

   Application.ScreenUpdating = False

'   Remember the current sheet
   Set UserSheet = ActiveSheet

'   Store info from the active sheet
   TopRow = ActiveWindow.ScrollRow
   LeftCol = ActiveWindow.ScrollColumn
   UserSel = ActiveWindow.RangeSelection.Address

'   Loop through the worksheets
   For Each sht In ActiveWorkbook.Worksheets
       If sht.Visible Then 'skip hidden sheets
           sht.Activate
           Range(UserSel).Select
           ActiveWindow.ScrollRow = TopRow
           ActiveWindow.ScrollColumn = LeftCol
       End If
   Next sht

'   Restore the original position
   UserSheet.Activate
   Application.ScreenUpdating = True
End Sub

> Is it possible to have worksheets scroll at the same time? For example when
> you scroll down to line 100 on sheet 1  and then you go to sheet 2 or 3 or 4
> and that sheet will be on line 100 also?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.