Troubles with this piece of code

[facu081088]

Code: Select all

Dim oRango As Object
Dim oBordeLinea As New com.sun.star.table.BorderLine	

.
.
.
.

With oBordeLinea
		.Color = RGB(255,0,0)
		.InnerLineWidth = 500
		.OuterLineWidth = 50
		.LineDistance = 20
	End With
NroCelda = "A" & oCelda.value & ":" & "P" & oCelda.value
oRango = ThisComponent.Sheets.getByName("hoja2").getCellRangeByName(NroCelda)
msgbox NroCelda

	With oRango
		.TopBorder.oBordeLinea				'Superior
		.BottomBorder. =oBordeLinea 		'Inferior
		.LeftBorder = oBordeLinea			'Izquierdo
		.RightBorder = oBordeLinea			'Derecho
	End With

This should format a range of cells, turning lines into red, but nothing happens.. any ideas??

Flame icon removed. That icon means 'Tagged to a known issue' (TheGurkha, Moderator)
Inserted

Code: Select all

 tags (RoryOF, Moderator)[/color]

[facu081088]

msgbox gives a correct range of cells, for example "A3:J3"

[hanya]

I could set cell borders with modified code below:

Code: Select all

Sub SetBorderColor
Dim oBordeLinea As New com.sun.star.table.BorderLine

With oBordeLinea
.Color = RGB(255,0,0)
.InnerLineWidth = 500
.OuterLineWidth = 50
.LineDistance = 20
End With
NroCelda = "A" & CStr(1) & ":" & "J" & CStr(1)
oRango = ThisComponent.Sheets.getByName("Sheet1").getCellRangeByName(NroCelda)
msgbox NroCelda

With oRango
.TopBorder = oBordeLinea 'Superior
.BottomBorder = oBordeLinea 'Inferior
.LeftBorder = oBordeLinea 'Izquierdo
.RightBorder = oBordeLinea 'Derecho
End With
End Sub

[FJCC]

This slightly modified version works for me.

Code: Select all

oSheet = ThisCOmponent.Sheets.getByName("Sheet2")
oCelda = oSheet.getCellrangeByName("A15")
Dim oBordeLinea As New com.sun.star.table.BorderLine
With oBordeLinea
.Color = RGB(255,0,0)
.InnerLineWidth = 50 'Changed from 500. The Linewidth is in steps of 0.01 mm, so 500 is very large
.OuterLineWidth = 50
.LineDistance = 20
End With
NroCelda = "A" & oCelda.value & ":" & "P" & oCelda.value  'This works, but oCelda.value is a number not a string.
oRango = ThisComponent.Sheets.getByName("Sheet2").getCellRangeByName(NroCelda)
msgbox NroCelda

With oRango
.TopBorder = oBordeLinea 'Superior Changed from TopBorder.oBordeLinea
.BottomBorder  = oBordeLinea 'Inferior
.LeftBorder = oBordeLinea 'Izquierdo
.RightBorder = oBordeLinea 'Derecho
End With

[facu081088]

I still have issues on this. This piece of code does nothing in my sheet, nor produces any error msg.. thanks guys, any other idea?

[RoryOF]

If it works for Hanya and FJCC, then it works. You may be doing something wrong and need to say exactly how you install the code and run the code.

[FJCC]

Here is a file with a working example. Push the button on Sheet2 to add a border to the row number entered in the neighboring cell.

[kingfisher]

facu081088, are you working with a spreadsheet document (Calc) or a text document (Writer)?

[facu081088]

it's an .ods. FJCC, your example starts with a red bordered row, but when I push the button, red lines go off and they never come again.
I'm new at this forum, and I couldn't find a way to send to you my sub. Either way, it appears that my problem it's not in my code, cos' this file you sent me doesn't work for me, but it does for you, tough. (sorry about my En.)

[hanya]

It seems you have the problem on the specifc office version, which version are you using?

[facu081088]

LibreOffice 3.4.4
OOO340m1 (Build:402)

[hanya]

I see, you are using LibreOffice, so border problem on spreadsheets is still there.
I tried FJCC's document on LibreOffice 3.5.1, it does not work at all.
And I do not know about build 402 but the document works on most recent version of Apache OpenOffice (AOO340m1 Rev1299571).

[facu081088]

I'll migrate, then. Thanks folks.

[rudolfo]

I remember a similar situation about 1 year back, I think I was using OOo 3.1.1 at that time. And I thought I did everything in the right way, but my borders did not show up. Until I gave it a try and did explicitly set the property .IsXXXXLineValid to True.
Maybe it is also related with the UNO structure "com.sun.star.table.TableBorder" that I am using and that isn't used in any of the other code samples that are posted here:

Code: Select all

Sub DrawBorder4Range( oRange As Object )
   Dim aTableBorder As New com.sun.star.table.TableBorder
   Dim aBorder

   aBorder = createUnoStruct("com.sun.star.table.BorderLine")
   With aBorder
      .Color = RGB(255,0,0)           ' 16711680 light red, 0 for black
      .OuterLineWidth = 50
      .InnerLineWidth = 50
      .LineDistance = 20
   End With

   With aTableBorder
      .TopLine = aBorder
      .RightLine = aBorder
      .BottomLine = aBorder
      .LeftLine = aBorder
      .Distance = 0
      .IsTopLineValid = True
      .IsRightLineValid = True
      .IsBottomLineValid = True
      .IsLeftLineValid = True
   End With
   
   oRange.TableBorder = aTableBorder
End Sub

[facu081088]

I've adapted my code, acording to your suggestions, but it still doesn't work. It says that "IsXXXXLineValid to True" (all the lines of this kind) are not recognized..

[RoryOF]

Have you the . before the IsXXXXLineValid statements?

[facu081088]

yes, of course. It's a record structure..

[rudolfo]

But you are aware of the difference in my code to the other suggestions and to your approach?!
In my code (which is surely not superior, but only happened to work for me) the last With statement is applied to a TableBorder UNO-Structure, not to a range object as in all the other cases.
Actually the range comes only into account in the very last line where its tableBorder property is set to the TableBorder UNO structure that I have just prepared before.

[facu081088]

Yes, i made those changes, too. I'll try again later, now I'm leaving.

[rudolfo]

Out of curiosity I have tested my code with ApacheOpenOffice 3.4.0 (340m1-Build:9586) with a sub that contains only the following single line:
Call DrawBorder4Range(ThisComponent.getCurrentSelection()).
And I see the expected behaviour: If I comment out the line .IsRightLineValid = True, note the line is only skipped, I don't set the property to False, then the red line of the right sight of the selected region doesn't show up. If all 4 isValid properties are set to True I got the full red rectangle.

For me this seems very much like there is something wrong in your user profile. Create another (non-privileged) user and start OpenOffice for this user to have it running with a fresh and innocent user profile.

[kingfisher]

This is based on a script which I have been using for years to set a bottom border only. I have edited the lines defining the colour to rgb because I don't remember where to get the number I use (which is 128 = black).

Code: Select all

Sub RangeBorder
' As always, test before using
Dim tBorder, tLeftLine, tTopLine, tRightLine, tBottomLine

tBorder = createUnoStruct( "com.sun.star.table.TableBorder" )
tLeftLine = createUnoStruct( "com.sun.star.table.BorderLine" )
tTopLine = createUnoStruct( "com.sun.star.table.BorderLine" )
tRightLine = createUnoStruct( "com.sun.star.table.BorderLine" )
tBottomLine = createUnoStruct( "com.sun.star.table.BorderLine" )

With tLeftLine
	.Color = rgb ( 255, 0, 0 )
	.InnerLineWidth = 0
	.LineDistance = 0
	.OuterLineWidth = 35
End With

With tTopLine
	.Color = rgb ( 255, 0, 0 )
	.InnerLineWidth = 0
	.LineDistance = 0
	.OuterLineWidth = 35
End With

With tRightLine
	.Color = rgb ( 255, 0, 0 )
	.InnerLineWidth = 0
	.LineDistance = 0
	.OuterLineWidth = 35
End With

With tBottomLine
	.Color = rgb ( 255, 0, 0 )
	.InnerLineWidth = 0
	.LineDistance = 0
	.OuterLineWidth = 35
End With

tBorder.IsTopLineValid = 1 : tBorder.IsLeftLineValid = 1
tBorder.IsBottomLineValid = 1 : tBorder.IsRightLineValid = 1

tBorder.LeftLine = tLeftLine : tborder.TopLine = tTopLine
tBorder.RightLine = tRightLine : tBorder.BottomLine = tBottomLine

ThisComponent.CurrentSelection.TableBorder = tBorder

End Sub