Want to disable scrollbar in split pane.

[Krayten]

Hi,

Hoping that some kind soul can help me here.

My custom spreadsheet employs a split pane view. So there is a
vertical scrollbar for the
top pane and a vertical scrollbar for the lower, more important pane.

The lower pane contains the view which I do not want end-users to be
able to scroll out of view. Only the
data in the top pane should be changed at any time ( with the view in
the lower changing automatically
as their values are formula linked to the top pane ).

Is there any way I can remove the scrollbar from the lower pane?

Thanks for reading, your time is appreciated.

 

[Krayten]

PS - In Tools/Options I can remove the scrollbar, but it removes both
of them (top and bottom pane).
It gets me closer to what I want to do, but the bottom pane is still
scrollable (with the mouse). Guess
I'm trying to lock the view of that pane. Possible?

 

[NickHK]

You can Freeze Panes, but this only affects the top (and left) panes.
So if you change you layout so the top (not bottom) panes is static, it is
easy.

Otherwise, you may be able to work something in the _SelectionChange events,
check which Pane is active and .Select an appropriate cell. Not perfect, but
maybe you can play with this:

Dim PaneRange As Range
Dim LastCell As Range

Const PANETOWATCH As Long = 2

Private Sub Worksheet_Activate()

With ActiveWindow.Panes(PANETOWATCH ).VisibleRange
Set PaneRange = .Resize(.Rows.Count - 2, .Columns.Count - 2)
End With

Set LastCell = ActiveCell

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If ActiveWindow.ActivePane.Index = PANETOWATCH Then
If Intersect(Target, PaneRange) Is Nothing Then
LastCell.Select
Else
Set LastCell = Target
End If
End If

End Sub


NickHK