Solved: Limiting scroll area is preventing me from clicking any row buttons
Attached sample Workbook
The code I'm using to limit the scrolling range is preventing an entire row from being selected.
Is there anyway to re-write it so that I can select a row?
[vba]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call LimitScrollArea
End Sub
Private Sub CommandButton1_Click()
Call ResetScrollArea
End Sub
' in a regular module
Sub LimitScrollArea()
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.ScrollArea = Range(Cells(1, 1), Cells(LastRow, 19)).Address
End With
End Sub
' in a regular module
Sub ResetScrollArea()
ActiveSheet.ScrollArea = ""
End Sub
[/vba]
Wish I could do something like this
[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call ResetScrollArea
If Not Me.RowButton.Clicked Then' incorrect syntax to demonstrate
Call LimitScrollArea
End If
End Sub[/vba]
My apologies, I was not really thinking clearly - that will not work, since the selection has not actually changed.
I cannot think offhand of how to do this.
I'd pieced together a work around that while it is not great,
it is very satisfactory for my requirement, so thought I would share just incase anyone finds it useful..
It does not limit scrolling using the scroll arrow on the Excel window,
but does limit the scroll range when scrolling while changing the selected cell using the keyboard right arrow key.
And, I can now select a Row, or column, as long as the activecell is in a column, that is less than #11 (11 can be changed, it's just in my case the viewable columns)
[vba]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LastRow As Long
With ActiveSheet
If ActiveCell.Column > 10 Then
'''''''''''''''''''''''''''''''''''''''''''''''''
ActiveWindow.ScrollColumn = ActiveCell.Column - 1
'keeps in view as many columns as possible, that are to the right of the selected cell
'''''''''''''''''''''''''''''''''''''''''''''''''