have sheets 2 and 3 scroll the same when scrolling sheet 1?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
that sheet will be on line 100 also?
 
Maybe with a little code:

I put this behind the ThisWorkbook module:

Option Explicit
Dim CurRow As Long
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Select Case LCase(Sh.Name)
Case Is = "sheet1", "sheet2", "sheet4"
If CurRow > 0 Then
Application.Goto Sh.Cells(CurRow, 1), scroll:=True
End If
End Select
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
Select Case LCase(Sh.Name)
Case Is = "sheet1", "sheet2", "sheet4"
CurRow = ActiveCell.Row
End Select
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

If you want to read more about these kinds of events:

Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm
 
Dave, thanks for your response. I do not know anything about macros, and I
was reading about them and still lost. Thanks again, I will keep trying.
 
You can try this against a copy of your workbook (just in case....).

Open excel and open your workbook.
rightclick on the excel icon to the left of the File|Edit|view|... menubar.

select view code.
Paste this code (I like it better than the first suggestion) into the codewindow
that just opened up (righthand side window)

Option Explicit
Dim CurRow As Long
Dim CurCol As Long
Dim ActCellAddr As String
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Select Case LCase(Sh.Name)
Case Is = "sheet1", "sheet2", "sheet4"
If CurRow > 0 Then
With Application
.EnableEvents = False
.Goto Sh.Cells(CurRow, CurCol), scroll:=True
Sh.Range(ActCellAddr).Select
.EnableEvents = True
End With
End If
End Select
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
Select Case LCase(Sh.Name)
Case Is = "sheet1", "sheet2", "sheet4"
CurRow = ActiveWindow.ScrollRow
CurCol = ActiveWindow.ScrollColumn
ActCellAddr = ActiveCell.Address
End Select
End Sub

Change the names in this line:
Case Is = "sheet1", "sheet2", "sheet4"
to match the sheets that should be affected -- in both spots, too. And make
sure you use all lower case letters.
 
Dave, I want to thank you for your help so far.
I did copy and paste the formula you sent me and changed the sheet names to
there correct names in both spots. After all is copied and changed, do I x
out of the box? And or how do I activate it. Also on top, it has 2 drop down
boxes, one box has general in it and the other has declarations.
 
These macros are events. They lurk behind excel waiting for you to do
something.

In this case, it's waiting for you to activate a sheet (click on the worksheet
tab at the bottom) or just change a selection (using the arrows or mouse or
whatever).

Make sure you put it in the ThisWorkbook module.

Make sure that macros are enabled when you open the workbook. You may need to
save, close and reopen to turn on macros for that workbook.
 
Dave, were getting closer. I copied and paste and changed the sheet names. I
then saved, closed out and reopened. It then ask me to enable macros I
clicked yes, when in and srolled down on a sheet and the others did not
follow :(
any other suggestions?
Thanks again
 
Did you put the code in the ThisWorkbook module?

billy said:
Dave, were getting closer. I copied and paste and changed the sheet names. I
then saved, closed out and reopened. It then ask me to enable macros I
clicked yes, when in and srolled down on a sheet and the others did not
follow :(
any other suggestions?
Thanks again
Click to expand...
 
I copied your formula, then opened my excel program, right clicked on the
excel icon in upper left hand corner, clicked on view code, paste in the
right box and changed the sheet names to what I called my sheets in lower
case letters. On top of the box where I paste the formula it says General
and declarations. I then closed out and saved changes. I went back the the
program and it asked me to enable macros. Is this correct?
 
Yes.

Then go back to one of the worksheets that you included in those lines. Scroll
down/change selection.

Then click on another one of the worksheets that you included.

You should see the same cell in the topleft corner of that visible window.

billy said:
I copied your formula, then opened my excel program, right clicked on the
excel icon in upper left hand corner, clicked on view code, paste in the
right box and changed the sheet names to what I called my sheets in lower
case letters. On top of the box where I paste the formula it says General
and declarations. I then closed out and saved changes. I went back the the
program and it asked me to enable macros. Is this correct?
Click to expand...
 
Dave,
I want to thank you for your help and patients.
I do have it working and it works great. The one thing I was doing wrong
was, when I would scroll down on sheet one, I would go to sheet two and
nothing would happen. I was not clicking on a cell before going to the next
sheet.
Thanks again!!!!
 
Ahhh. The selection change event actually wants you to change the selection.
Just scrolling isn't enough. But you found that out the hard way <vbg>.

billy said:
Dave,
I want to thank you for your help and patients.
I do have it working and it works great. The one thing I was doing wrong
was, when I would scroll down on sheet one, I would go to sheet two and
nothing would happen. I was not clicking on a cell before going to the next
sheet.
Thanks again!!!!
Click to expand...
 
Back
Top