Re: Scrolling a spreadsheet with a macro

Den 2011-03-04 17:56:12 skrev Niklas Nebel <[email protected]>:

> On 04.03.2011 17:18, Johnny Rosenberg wrote:
>> Have been looking for it with Xray, but so far I didn't find anything
>> that I think does what I want.
>>
>> What I want to do is to search for a cell. When I find it, I want it to
>> appear as the first row and the the first column. Let's say that I find
>> what I'm looking for in C7. Then I want the sheet to scroll so that it
>> appears like this:
>>
>> C D E F G H I J…
>> 7
>> 8
>> 9
>> 10
>> 11
>> 12
>> 13
>> 14
>> 15
>> ⁝
>
> Use the view's XViewPane interface. In Basic that's just
> oView.FirstVisibleColumn = 2
> oView.FirstVisibleRow = 6
>
> Niklas


I just ran into a problem doing this. The problem is that I have ”fixed”  
the sheet, so I am always able to see the first rows and the first column,  
so the A column and the 4 first rows are always visible.

I always want A1 to be selected, keeping the selection out of the  
important area. However, A1 is above the ”fix point” and therefore I can't  
scroll unless I first select a cell below the ”fix point” and then  
reselect A1 again.

Here's my Scroll subroutine:

Sub Scroll(Col As Integer, RowOMA As Integer, Row As Integer)
	Dim Cell As Object
	Cell=ThisComponent.getSheets().getByName("Data").GetCellbyPosition(0,Row+1)
	ThisComponent.CurrentController.Select(Cell)

	ThisComponent.getCurrentController().setFirstVisibleColumn(Col)
	ThisComponent.getCurrentController().setFirstVisibleRow(RowOMA)

	Cell=ThisComponent.getSheets().getByName("Data").GetCellbyPosition(0,0)
	ThisComponent.CurrentController.Select(Cell)
End Sub

Row is the last used row in my sheet
Col is always 0, but I implemented it anyway in case of future changes…
RowOMA means ”Row One Month Ago” (column A is a date column and I only  
want to see the last month, that is, for example, 2011-02-11 – 2011-03-11  
or 2011-02-28 – 2011-03-31). I add one new row every day, no more, no less.

The first part works great; the right cell is selected.
Next part also works great; sheet is scrolled the way I want.
The last part however… Yes, it selects A1 just like it should, but it does  
it like the sheet was not fixed, so the scroll thing becomes meaningless!

For example: Last used row is 864 today. Today's date is 2011-03-04, which  
means that my DateOMA (used in another subroutine) is 2011-02-04 which is  
found at row 836, so:
Col=0
RowOMA=836
Row=864

The first thing that now happens is that row 865 is selected. Excellent.
Next, the sheet is scrolled so that the first rows I can see are the  
following (the sheet is fixed, as I said):
   1
   3 (Row 2 is hidden)
   4
836
837
838
  ⁝
Excellent again.

The last part of the subroutine now make sure that A1 is once again  
selected. What I see now are the following rows:
   1
   3
   4
   5
   6
   7
  ⁝

NOT so excellent…
Is there a way to select a cell that works the same way as if you only  
clicked that cell with your mouse, even if the sheet is fixed?

When I say ”fixed”, I am referring to Window → Fix. In my case I selected  
B5, then I clicked Window → Fix. This makes the A column and the first  
four rows fixed, so when I scroll manually I can scroll everything except  
the A column and rows 1–4.

-- 
Kind regards

Johnny Rosenberg
--
-----------------------------------------------------------------
To unsubscribe send email to [email protected]
For additional commands send email to [email protected]
with Subject: help