[RESOLVED] Scrolling an Excel sheet using VB6

[Spoo]

I have an app in VB6 that does some calcs and posts
the results on an Excel worksheet (WS10).

I'd like to effectively perform a "scoll" on WS10.

Here's what I have now:

For rr = 11 to 80
   DoCalcs rr
   If Calc1 = Empty
      Exit For
   EndIf
   WS10.Cells(rr, 2) = Calc1
   WS10.Cells(rr, 3) = Calc2
Next rr

This is working just fine.

Basically, the app calls DoCalcs, does the calcs, stores
the values in vars Calc1 and Calc2, and paints the results
in col 2 and 3 of WS10.

The loop is exited once Calc1 is Empty .. it's a long story, but
this is by design. I've simplified the above code for discussion
purposes.

Here is the rub. I can see rows 1 thru 50 without scrolling.
Up to now, the loop is exited prior to rr = 50, so, no issue.

I am now at the point where the loop will go, say, to rr = 55,
which means that I won't be able to follow the data as it is being
painted to the WS -- it will be off the bottom of the screen.

The WS is in FreezePanes -- ie, top 10 rows are fixed; scrolling
using the scrollbar begins by moving row 11, etc

What I'd like to do is, when rr=45, move the top row to be 45.

If this were a FlexGrid -- no probs -- FG1.TopRow = 45.
What is the syntax to do the same thing to WS10?

Spoo

[RobDog888]

Record a Macro in Excel so you can see how Excel does it. then use a variation of that code in VB6 as its almost identical.

[Spoo]

RobDog

Sorry.. I should have said that I tried that already.
As I manually scroll the WS, I get the following Macro results:

Sub Macro1()
    '
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 11
    '
End Sub

There is no parallel method in VB6 (as far as I can see).

The VB Editor shows me:

WS10.Activate
WS10.Application ... but no ActiveWindow

WS10.Rows ......... but no ScrollRow (or TopRow, which is what I really want)
WS10.ScrollArea ... but no ScrollRow

What am I missing?

Spoo

[RobDog888]

Just look at Excels Object Browser after you generated that code. If you right click on ActiveWindow and select Definition it will attempt to open in the Object Browser. If it suceeds then you will see the parent/owner of the method/property. If Hidden Members are set to not display then it will error and tell you it can not. Just right click anything in the main window pane of the Object Browser and select "Show Hidden Members".

Ultimately you can find that the Application object is the parent of it.

Property ActiveWindow As Window
read-only
Member of Excel.Application

Application.ActiveWindow.ScrollRow = 888

So you should have an application object instanciated and defined in your code before you open your workbook in code.

oApp.ActiveWindow.ScrollRow = 888

[Spoo]

RobDog

Brilliant.
This is how it panned out..

' declarations in a module
Public WB10 As Excel.Workbook
Public WS10 As Excel.Worksheet

' code in another module
src = "C:\XL stuff\myWB.xls"
Set WB10 = GetObject(src)
Set WS10 = WB10.Worksheets("my sheet")

WB10.Application.ActiveWindow.ScrollRow = 11
WB10.Application.ActiveWindow.ScrollRow = 30

The ScrollRow then works as an effective "TopRow".

BTW, I did try your suggested right click on the macro code,
and did succeed in bringing up the Object Browser. Yes, it
is all there, but as I'm a first time user of that guy, I doubt
I'd have come up with the appropriate dot references ...

... so, a big thank you for providing it.

EDIT: This thread is hereby declared resolved, and I am unanimous in that

Spoo