BASIC+Calc: How to Freeze Rows and Columns

lonk · October 15, 2019, 8:28am

From this post, I have edited in order to freeze on a specific Sheet and it is not an active Sheet.

Dim oDoc As Object
oDoc = ThisComponent
oDoc.CurrentController.FreezeAtPosition(0,6)

It freezed on the active Sheet.

Can you please help me freeze on a specific Sheet which is not an active Sheet ?

mauricio · October 15, 2019, 1:42pm

This option, have sense only in visible sheet, so, you can apply when activate or activate and return to current activate sheet.

doc = ThisComponent
cc = doc.CurrentController
current = cc.Selection
sheet = doc.Sheets(0)
cc.select(sheet)
cc.freezeAtPosition(5,5)
cc.select(current)

lonk · October 15, 2019, 2:01pm

@mauricio

Thank you so much for code and your explanation.

schiavinatto · October 15, 2019, 3:32pm

.

Sub FreezeRowsColumns
    TCCC = ThisComponent.CurrentController 
    TCCC.select( ThisComponent.getSheets.getByName( "Plan2" )
    TCCC.freezeAtPosition(6,5)
    TCCC.select(current)
    End sub

lonk · October 15, 2019, 5:17pm

@mauricio

Sub FreezePane
'	Dim doc As Object : doc = ThisComponent
'	Dim cc As Object : cc = doc.CurrentController
'	Dim current As Object : current = cc.Selection
	Dim current As Object : current = ThisComponent.CurrentController.Selection
	ThisComponent.Sheets(0) 'Dim sheet As Object : sheet = doc.Sheets(0)
	ThisComponent.CurrentController.Select(ThisComponent.Sheets(0))	'cc.Select(sheet)
	ThisComponent.CurrentController.FreezeAtPosition(5,5) 'cc.FreezeAtPosition(5,5)
	ThisComponent.CurrentController.Select(current)	'cc.Select(current)
End Sub

Is it possible to avoid variable namely ‘current’ ?

mauricio · October 15, 2019, 5:33pm

I don’t understand, “avoid”, you need save your reference to current sheet in some place if you want return after, but… that large lines are ugly for me.

schiavinatto · October 15, 2019, 5:42pm

.This one has freezing it removes and if it has no places.

sub FreezeRowsColumns2
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint" : args1(0).Value = "Plan1.$B$7"
createUnoService("com.sun.star.frame.DispatchHelper").executeDispatch(ThisComponent.CurrentController.Frame, ".uno:GoToCell", "", 0, args1())
createUnoService("com.sun.star.frame.DispatchHelper").executeDispatch(ThisComponent.CurrentController.Frame, ".uno:FreezePanes", "", 0, Array())
end Sub

lonk · October 15, 2019, 11:32pm

@mauricio

Thank you so much for your explanation.
So, that variable is for keeping the initial location.

And sorry for my long and disgusting code. This week I have just started learning LO BASIC after breaking up with MS Excel VBA which I have ever used for decades.

mauricio · October 16, 2019, 12:03am

If you started with macros in LibreOffice, I recommended you start with Python, not Basic. Best regards.

P.S. Many year ago, I wrote macros in VBA too.

lonk · October 17, 2019, 1:44am

Dear @mauricio ,

Thank you so much for your concern.

After finishing my project on LO BASIC Calc, I will learn LO Python Calc.

Now many people are heading for LO Python Calc.

Can you please tell me why they prefer LO Python Calc in brief?

mauricio · October 17, 2019, 2:41am

Where finish Basic, barely start Python.