Apache OpenOffice Community Forum

Posted: **Thu Apr 01, 2010 8:42 pm**

How do I tell Calc to scroll to the top of a worksheet in Basic without activating the sheet?

Background: I have a spreadsheet with several worksheets. I have written a macro to sort the data in all of the worksheets each time the spreadsheet is opened. After the data are sorted, I want to make sure that the individual worksheets are scrolled up to the top, so that the most important data (which are at the top) are immediately visible when a user clicks on a worksheet. I have been doing this as follows:

Code: Select all

    oRange = oSheet.getCellRangeByName("A2")
    ThisComponent.getCurrentController.select(oRange)

where the column heads are in row 1, and row 2 is the first data row. But when I do this, Calc visible flashes through all the worksheets as it processes them, which is annoying. So is there a way of doing the same thing for hidden worksheets without making them visible?

I am using OO 3.0.0 on Ubuntu 8.10. Any suggestions would be very welcome.

Many thanks,
graneezle

Posted: **Fri Apr 02, 2010 8:12 am**

I am using two routines to disable or allow screen update. I launch ScreenUpdatingOff at the beginning of a macro and ScreenUpdatingOn at the end.

Code: Select all

Sub ScreenUpdatingOn
' This routine allows screen updating

   ThisComponent.UnlockControllers
   ThisComponent.removeActionLock

End Sub


Sub ScreenUpdatingOff
' This routine blocks screen updating and 
' therefore allows faster macro execution

   ThisComponent.addActionLock
   ThisComponent.LockControllers

End Sub

Posted: **Fri Apr 02, 2010 5:52 pm**

Thanks, squenson. This works very well to hide the intra-worksheet updates (e.g., sorting), and it does seem to speed up the script as a whole. But when you're on sheet A, and the script goes to work on sheet B, the screen still jumps to sheet B. Anyone know if there's a way of preventing this?

Posted: **Fri Apr 02, 2010 6:35 pm**

With ViewData, for example to set A2 to left top position and cursor to A2 on the first sheet:

Code: Select all

sub SetViewData
  oDoc = ThisComponent
  oController = oDoc.getCurrentController()
  sViewData = oController.getViewData()
  
  sParts = Split(sViewData, ";")
  
  If UBound(sParts) >= 3 Then
    sParts(3) = "0/1/0/0/0/0/2/0/0/0/1"
  End If
  sViewData = Join(sParts, ";")
  oController.restoreViewData(sViewData)
end sub

Here is example of a ViewData and it can be divided with ";".

Code: Select all

100/60/0;0;tw:270;3/13/0/0/0/0/2/0/0/0/1;5/15/0/0/0/0/2/0/0/0/0;0/0/0/0/0/0/2/0/0/0/0

100/60/0 means zoom value of normal mode/zoom value of page break preview/view mode. 0 for active sheet number. tw:270 means unknown/position of horizontal scroll bar. And others are describes view state of each sheet.

Descriptions about sheet, each part means:

0: cursor position column

1: cursor position row

2: column split mode

3: row split mode

4: vertical split position

5: horizontal split position

6: index of focused window

7: left top column index of left side window

8: left top column index of right side window

9: row index of upper window

10: row index of window below

But index 7 for column position of left top cell and 10 for its row position are used for non-splitted window.

Apache OpenOffice Community Forum

Scroll to top of calc sheet / Basic

Scroll to top of calc sheet / Basic

Re: Scroll to top of calc sheet / Basic

Re: Scroll to top of calc sheet / Basic

Re: Scroll to top of calc sheet / Basic