Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
have sheets 2 and 3 scroll the same when scrolling sheet 1?
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
have sheets 2 and 3 scroll the same when scrolling sheet 1?
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 billy boy wrote: 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? -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
have sheets 2 and 3 scroll the same when scrolling sheet 1?
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. "billy boy" wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
have sheets 2 and 3 scroll the same when scrolling sheet 1?
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. billy boy wrote: 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. "billy boy" wrote: 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? -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
have sheets 2 and 3 scroll the same when scrolling sheet 1?
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. "Dave Peterson" wrote: 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. billy boy wrote: 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. "billy boy" wrote: 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? -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
have sheets 2 and 3 scroll the same when scrolling sheet 1?
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. billy boy wrote: 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. "Dave Peterson" wrote: 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. billy boy wrote: 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. "billy boy" wrote: 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? -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
have sheets 2 and 3 scroll the same when scrolling sheet 1?
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 "Dave Peterson" wrote: 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. billy boy wrote: 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. "Dave Peterson" wrote: 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. billy boy wrote: 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. "billy boy" wrote: 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? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofilter Lists across Multiple Sheets, Maintain Correct Referenc | Excel Worksheet Functions | |||
Sorting Data from 2 sheets, one sheet which is already sorted | Excel Worksheet Functions | |||
multiple sheets vs. 1 sheet | Excel Worksheet Functions | |||
Lookup values in multipul sheets and show value in another sheet | Excel Worksheet Functions | |||
linking multiple sheets to a summary sheet | Excel Discussion (Misc queries) |