BASIC+Calc: How to expand the height of a row or all rows having wrapped cells and under protected sheet?

[lonk]

REM  *****  BASIC  *****
Option Explicit
Sub AddANewLineInACellAndWrapCell
	Dim	oDoc	As Object : oDoc = thisComponent
	Dim oSheet	As Object : oSheet = oDoc.CurrentController.ActiveSheet
	Dim oCell	As Object
	oCell = oSheet.getCellByPosition(0, 0)
	oCell.setPropertyValue( "IsTextWrapped", True )
	oCell.String = "A" & Chr(10) & "B" & Chr(10) &"C" & Chr(10) &"D"
End Sub

What code lines should be added in order to expand the height of row 0 or all rows ?
LOCalcBASIC_AddANewLineInACellAndWrapCell.ods (13.0 KB)
Linux Mint 20.2 Cinnamon DE
Version: 7.2.2.2 / LibreOffice Community
Build ID: 20(Build:2)
CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Ubuntu package version: 1:7.2.2~rc2-0ubuntu0.20.04.1~lo1
Calc: threaded

[Zizi64]

Your macro works fine in my LibreOffice 6.1.6 version on Win10.
(If you modified the macro for the current selected Cell - then you can try it on many cells and many rows:

'oCell = oSheet.getCellByPosition(0, 0)
oCell = ThisComponent.getCurrentSelection()

And I launched the macro by a form control element (a Button) placed on the Sheet.
The height of the modified cells (rows) have been modified automatically.

LOCalcBASIC_AddANewLineInACellAndWrapCell_Zizi64.ods (14.6 KB)

But, your sheet was protected. Fortunately it was protected without any password. I just unprotected the sheet (manually) before I made the modifications. And I used the macro without the Sheet protection.

[sokol92]

Add the following line to the end of the macro from the start message.

oSheet.Rows(0).OptimalHeight=True

[lonk]

It is automatically expanded right from the beginning if the sheet is not locked. But I need it to be expanded within protected sheet .

[sokol92]

Let’s do it together.

• Open the file from the start message and add the line I specified to the end of the macro.
  Change the assigned cell value in macro to, for example, "A".
• Execute the macro (without removing the protection from the sheet). The height of the first row has changed!
• Change the assigned cell value in macro to "A" & Chr(10) & "B" and execute macro. The height of the first row has changed again.

[Zizi64]

You can switch OFF and then you can switch ON the protection by your macro. The user can not do anything in the very short time, during the sheet is unprotected.

[lonk]

Thank you so much.

REM  *****  BASIC  *****
Option Explicit
Sub AddANewLineInACellAndWrapCell1Line
	Dim	oDoc	As Object : oDoc = thisComponent
	Dim oSheet	As Object : oSheet = oDoc.CurrentController.ActiveSheet
	Dim oCell	As Object
	oCell = oSheet.getCellByPosition(0, 0)
	oCell.setPropertyValue( "IsTextWrapped", True )
	oCell.String = "A"
	oSheet.Rows(0).OptimalHeight=True
End Sub

Sub AddANewLineInACellAndWrapCell4Lines
	Dim	oDoc	As Object : oDoc = thisComponent
	Dim oSheet	As Object : oSheet = oDoc.CurrentController.ActiveSheet
	Dim oCell	As Object
	oCell = oSheet.getCellByPosition(0, 0)
	oCell.setPropertyValue( "IsTextWrapped", True )
	oCell.String = "A" & Chr(10) & "B" & Chr(10) & "C" & Chr(10) &"D"
	oSheet.Rows(0).OptimalHeight=True
End Sub

oSheet.Rows(0).OptimalHeight = True is for a single row.
Is only a loop used for multiple and continuous rows?

[Zizi64]

The usage of a loop is a clear and simple approach.
…
Please download the XrayTool and use it. Then you will able to examine the programming objects, and you (maybe) will able to use an another approach too.

oRange = oSheet.GetCellRangeByName("A1:AMJ3")
xray oRange 'you will see: the oRange has property named Rows.
oRows = oRange.Rows	
xray oRows 'and you will see: the oRows has property named OptimalHeight
oRows.OptimalHeight = True

XrayTool:
https://berma.pagesperso-orange.fr/index2.html

[sokol92]

@lonk

Is only a loop used for multiple and continuous rows?

We continue our research on the same document (and the protected sheet).

Sub AddANewLineInACellAndWrapCellLines2
   Dim oSheet As Object
   oSheet = ThisComponent.CurrentController.ActiveSheet
   With oSheet.getCellRangeByPosition(0,0,0,2)
     .getCellByPosition(0,0).setString "A"
     .getCellByPosition(0,1).setString "A" & Chr(10) & "B"
     .getCellByPosition(0,2).setString "A" & Chr(10) & "B" & Chr(10) & "C"
     .IsTextWrapped=True
     .Rows.OptimalHeight=True
   End With  
End Sub

For the specifics of the OptimalHeight property, see here.