PDA

View Full Version : OWC Spreadsheet Scrolling



sryabinin
07-31-2008, 07:16 AM
I'm not sure if this should be posted in a different forum but since I am working in excel I think it applies here too..

I have a form with several OWC spreadsheets on it. Right now they scroll independently of one another. What I need to do is to syncronize the scrolling between two of the spreadsheets. I dont know if this is the right way to go about it but I decided to use a ScrollBar control object and somehow link both the Spreadsheet objects to the scroll bar, this was before I realized that Spreadsheet objects dont have a Scroll method. So now I am out of ideas.

I could consolidate spreadsheets into one large one and use freeze some of the columns but that makes complications with other bits of the macro I am trying to write.

Anyone have an idea on how to get this working?

TomSchreiner
07-31-2008, 11:21 AM
This will provide "proportionate" scrolling. That means, both spreadsheets will scroll together according to the scrollbar's value. However, there is no guarantee, unless you make it so, that the user will not scroll the worksheet by scrolling ranges by way of selecting cells.

If, at any time, you need to sync the position of both worksheets, see the ActiveWindow.ScrollIntoView method. See the attached or build from below.

UserForm1, SpreadSheet1 and 2, ScrollBar1.

Option Explicit

Private CurrentSBVal As Integer

Private Sub ScrollBar1_Change()
If CurrentSBVal < ScrollBar1.Value Then
Spreadsheet1.ActiveWindow.SmallScroll (ScrollBar1.Value - CurrentSBVal)
Spreadsheet2.ActiveWindow.SmallScroll (ScrollBar1.Value - CurrentSBVal)
Else
Spreadsheet1.ActiveWindow.SmallScroll , (CurrentSBVal - ScrollBar1.Value)
Spreadsheet2.ActiveWindow.SmallScroll , (CurrentSBVal - ScrollBar1.Value)
End If
CurrentSBVal = ScrollBar1.Value
End Sub

Private Sub UserForm_Activate()
Spreadsheet1.ActiveWindow.DisplayVerticalScrollBar = False
Spreadsheet2.ActiveWindow.DisplayVerticalScrollBar = False
ScrollBar1.Min = 1
ScrollBar1.Max = (Spreadsheet1.ActiveSheet.UsedRange.Rows.Count + _
Spreadsheet1.ActiveSheet.UsedRange.Rows.Count) / 2
ScrollBar1.SmallChange = 1
ScrollBar1.LargeChange = Spreadsheet1.ActiveWindow.VisibleRange.Rows.Count
CurrentSBVal = ScrollBar1.Value
End Sub