VBA -- Scroll selected cell to top of window

[SydneyGeek]

Hi, I'm building a navigation screen for users to get down a long worksheet.
Progress so far:

1. User selects a heading from a drop-down list and hits "Go" button.
2. Code builds an array of all cells in Column A used range with a yellow background (this is in case user adds/deletes rows) -- row numbers added to array.
3. Cell A10 has a calc showing which item in the list was selected
4. Cell (Array item #,1) is selected.

So far, so good. Trouble is, when the cell is selected it's vertically centred in the scrollable window (I have Window | Freeze panes on).
Question: WITHOUT hiding rows, how do I position the selected cell at the top of the scrollable section?

Any help appreciated -- code is shown below.
Denis

Sub ArrayNav()
Dim c As Range
Dim i As Integer, R As Long
Dim ListPos As Integer
Dim TopRow As Long
Dim BottomRow As Long
Dim MyVar()


Application.ScreenUpdating = False
'find the position in the list
Range("A10").Calculate 'Workbook calculation is set to Manual
ListPos = Range("A10").Value
'this routine builds an array of all heading rows in Col A, plus last used cell.
R = Range("A65536").End(xlUp).Row
Range("A:A").SpecialCells(xlCellTypeConstants).Select
i = 0
For Each c In Selection
    If c.Interior.ColorIndex = 6 Then 'Yellow background=heading
        i = i + 1
        ReDim Preserve MyVar(i)
        MyVar(i) = c.Row
    End If
Next c
i = i + 1
ReDim Preserve MyVar(i) 'Add value for last used row
MyVar(i) = R
Range("A11").Select
BottomRow = MyVar(ListPos)
Cells(R, 1).Select
Application.ScreenUpdating = True
Cells(BottomRow, 1).Select
End Sub

 

[Derek]

Hi there

Perhaps you can try something like this:

ActiveWindow.ScrollRow = ActiveCell.Row

This should place the active cell as the first row in your frozen pane

regards
Derek

 

[Ekim]

I’m having difficulty in duplicating your set-up.

1. What’s in cell A10? A formula?
2. Is the drop down a simple validation drop down?

In short, how is the drop down selection connected to Cell A10?

I thought that the set-up right, but keep getting a “type mismatch” error on
ListPos = Range("A10").Value

Regards,

Mike

 

[SydneyGeek]

Hi Derek, that would work but with the length of the worksheet (several hundred rows), it may drive the users nuts.

APOLOGIES -- That's spot on!!

Mike, to answer your questions,
1. Yes, I'm using a simple validation list, in cell B1. The list is a named range (Categories) containing the headings, in the order that they appear on the sheet.
2. A10 contains a Match calculation: =MATCH(B1,Cost_Categories,0).
3. Row 11 is the top of the scrollable region.

Hope this helps you make sense of the setup.

Denis

 

[Ekim]

Managed to get your routine working. Suggested changes:

Convert your macro to an Event routine. A selection from the validation drop down will automatically take you to the selected heading i.e. you will not have to click a macro button. The following macro in the relevant sheet module works in Excel 2000 upwards but not Excel 97:

Private Sub Worksheet_Change(ByVal Target As Range)

‘ your code
ActiveWindow.ScrollRow = ActiveCell.Row   ‘ from Derek

End Sub

At each heading, put a “Go Top” button assigned to:

Sub mTopp()
If ActiveSheet.Name = "Sheet1" Then _
  Application.Goto Reference:="mTop"
End Sub

Where “mTop” is a defined name that refers to cell A1

Regards,

Mike

 

[SydneyGeek]

Thanks Mike,
I've used Derek's solution for now bit it's great to have more than one possible solution -- one of the best things about this site!

Pretty sure I can find a use for your idea.

Thanks again

Denis

 

[webmerlin]

I have used the following to achieve something similar;

ActiveWindow.SmallScroll Up:=200
Cells(1, 1).Select
ActiveWindow.SmallScroll down:=3
cells(4,1).Select


If you scroll up (set a large numner) VBA does not error if you ask it to scroll beyond the top - it just goes to the top. Then if you know the row you want to scroll doen to you can specify the reqiured numer to scroll down.

 

[Mackeral]

Here is a page I just found on Microsoft about this issue:

Identify First Visible Row of Scrolled Excel Worksheet


It makes it easy to program.