Synchronized scrolling of side-by-side windows?

sleepdoc

New Member
Joined
Jul 28, 2005
Messages
41
Gents and ladies,

I was recently shown by "Gerald Higgins" (a helpful poster on this forum)how to look at 2 sheets within the same file, side by side. He showed me the feature in this post here....

http://www.mrexcel.com/board2/viewtopic.php?t=297369&highlight=

Now i need to find a way to scroll both sheets in a synchronized fashion. In other words, I would like to tile them left and right of eachtoher. Then align them in some fashion (for this example, lets say line 32 on the left side is directly aligned to line 32 on the right side). Then when i scroll one of them, the other one scrolls identically, line for line.

as it stands now, i have to scroll all over the place to find something in the first sheet, and then go scroll the second shet until i find the samer section. in other words, both sheets are formatted exactly the same.

Any ideas would be apprecaited. Just looking for an efficiency trick here. scrolling them both is silly and time consuming.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
It just so happens I wrote something that may be of use to you, it would need a little tweaking though.

I need to similarly scroll round in a synchronised fashion - but in my case it's two windows onto the same sheet (sheet1.xls;1 and sheet1.xls;2) say. So you'll need to tweak the bit where it activates each subwindow and get the sheet names in - use the Sheets object to populate a form with tick boxs? Anyway doesn't matter, it's fairly straightforward.

To do the synchronised scrolling, create a small form. YOu need two scrollbars, you can see the name in the code, a spin button, a caption, a label and a tick box.

Here's the code:
Code:
Private Sub UserForm_Initialize()
        
    ActiveWindow.Zoom = 100
        
    With SpinButton2
        .Value = 100
        .Min = 10
        .max = 400
        .Value = ActiveWindow.Zoom
    End With
    
'   Horizontally scrolling
    With ScrollBarColumns
        .Min = 1
        .max = 256
        .Value = ActiveWindow.ScrollColumn
        .LargeChange = 25
        .SmallChange = 1
    End With
    
'   Vertically scrolling
    With ScrollBarRows
        .Min = 1
        .max = ActiveSheet.Rows.count
        .Value = ActiveWindow.ScrollRow
        .LargeChange = 25
        .SmallChange = 1
    End With
    
    On Error Resume Next
    
    Windows(ActiveWorkbook.Name & ":2").Activate
    If Err Then
        Label1.Enabled = False
        CheckBox1.Enabled = False
    Else
        Label1.Enabled = True
        CheckBox1.Enabled = True
        Windows(ActiveWorkbook.Name & ":1").Activate
        Range("A1").Select
        Windows(ActiveWorkbook.Name & ":2").Activate
        Range("A1").Select
        Windows(ActiveWorkbook.Name & ":1").Activate
    End If
    On Error GoTo 0
    
End Sub

Private Sub SpinButton2_Change()

If CheckBox1.Enabled = False Then
    With ActiveWindow
        .Zoom = SpinButton2.Value
        .ScrollColumn = ScrollBarColumns.Value
        .ScrollRow = ScrollBarRows.Value
    End With
Else
    Windows(ActiveWorkbook.Name & ":1").Activate
    With ActiveWindow
        .Zoom = SpinButton2.Value
        .ScrollColumn = ScrollBarColumns.Value
        .ScrollRow = ScrollBarRows.Value
    End With
    Windows(ActiveWorkbook.Name & ":2").Activate
    With ActiveWindow
        .Zoom = SpinButton2.Value
        .ScrollColumn = ScrollBarColumns.Value
        .ScrollRow = ScrollBarRows.Value
    End With
    Windows(ActiveWorkbook.Name & ":1").Activate
End If

ZoomForm.Caption = ActiveWindow.Zoom & "%"

End Sub

Private Sub ScrollBarColumns_Change()
    If CheckBox1.Enabled = False Then
        ActiveWindow.ScrollColumn = ScrollBarColumns.Value
    Else
        Windows(ActiveWorkbook.Name & ":1").Activate
        ActiveWindow.ScrollColumn = ScrollBarColumns.Value
        Windows(ActiveWorkbook.Name & ":2").Activate
        ActiveWindow.ScrollColumn = ScrollBarColumns.Value
        Windows(ActiveWorkbook.Name & ":1").Activate
    End If
End Sub

Private Sub ScrollBarRows_Change()
    If CheckBox1.Enabled = False Then
        ActiveWindow.ScrollRow = ScrollBarRows.Value
    Else
        Windows(ActiveWorkbook.Name & ":1").Activate
        ActiveWindow.ScrollRow = ScrollBarRows.Value
        Windows(ActiveWorkbook.Name & ":2").Activate
        ActiveWindow.ScrollRow = ScrollBarRows.Value
        Windows(ActiveWorkbook.Name & ":1").Activate
    End If
End Sub

Private Sub OKButton_Click()
    Unload Me
End Sub

Now you can hopefully see that all you need to do is:

1 Get the names of your two sheets, activate one, open a new window on the workbook with

Code:
    ActiveWindow.NewWindow

they will both have the same sheet activated so in say sheet1.xls;2 activate the second sheet


next arrange the sheets - use one of

Code:
    Windows.Arrange ArrangeStyle:=xlVertical
    Windows.Arrange ArrangeStyle:=xlHorizontal

you should then be more or less able to drop the code above into a macro in a form, attach a button to the toolbar and bob's your mothers special friend as they say

Hope this helps!

Forgot to add, as the code tests for two windows at the start it acts as an interactive zoom on the current sheet. YOu could save yourself time by just creating the code in personal.xls to open the form up, create the form, drop the code above into the form and then when you want to use it manually open up a new window and select the sheet. It's probably easier!
 
Upvote 0
Wow. That was qiuck!. and ot be honest, a bit over my head. I havent written code in 15 years and i have never coded macros (or whatever this code bit is called) in excel ever. Maybe this is my opportunity to learn how to by using your code and tweaking it. I'm not really sure where to start though.

Let me re read your email a few times and see what I can figure out. I'm not even sure where the code goes, how to compile it, how to run it etc.

was hoping it was a feature that already existed in Excel or someone knew of a third party add-on that already had this feature built into it.
 
Upvote 0
Well I'm afraid I can't teach it you here. However if you fancy messing around here's the general scheme of things

You go into VBA. Open up project explorer from the View menu. Find personal.xls - this is a file that XL keeps your defaults in. Select the file and you need to add a module. You then need to add a userform called zoomform.

In the module you added, type the following:
Code:
Sub ZoomMacro
    ZoomForm.Show
End Sub
Then get the userform up from the project explorer
Open up the toolbox (it may do it for you), and add the controls above - scrollbars, spinbuttons, labels etc.

Go through the code, where it looks like one of the controls - look for where it says <item>.enabled or <item>.value then you need to find the control on your userform, open up the properties by rightclicking over the control and change the name in properties to the name in the code.

That's enough to get you started.

If you want me to send you a copy of personal.xls which has a lot of useful macros with forms already set up, PM me with your email address.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top