Border Macro No Longer Works

A couple years ago some helpful soul on this forum got me to Record a Macro to create a Thick Border around the outside of the selected cell group (outerBorder) and a Thin Border between the cells (innerBorder). I used it successfully at the time and several times since then. I added it to the Toolbar so it would be easy to use.

Recently I needed to have that functionality again but my Toolbar button was not working. It only added the Outer Border.

I finally decided to Record a new Macro. When I run the Macro, it does the same as my old Macro did. I draws the Outer Border but no Inner Borders. Since this used to work I am assuming it is a bug but I haven’t been able to find any info on it or a workaround.

Here is the Macro code:

sub ThickThin
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(7) as new com.sun.star.beans.PropertyValue
args1(0).Name = "BorderOuter.LeftBorder"
args1(0).Value = Array(0,0,71,0,0,71)
args1(1).Name = "BorderOuter.LeftDistance"
args1(1).Value = 0
args1(2).Name = "BorderOuter.RightBorder"
args1(2).Value = Array(0,0,71,0,0,71)
args1(3).Name = "BorderOuter.RightDistance"
args1(3).Value = 0
args1(4).Name = "BorderOuter.TopBorder"
args1(4).Value = Array(0,0,71,0,0,71)
args1(5).Name = "BorderOuter.TopDistance"
args1(5).Value = 0
args1(6).Name = "BorderOuter.BottomBorder"
args1(6).Value = Array(0,0,71,0,0,71)
args1(7).Name = "BorderOuter.BottomDistance"
args1(7).Value = 0

dispatcher.executeDispatch(document, ".uno:BorderOuter", "", 0, args1())

Hello @Kitfox,

looking into this right now, i just saw that your macro code is incomplete.
instead of typing pre + code tag yourself, you could just select the macro code and press the “101” button.

I changed it as you suggested.

Better already :slight_smile:
But i’m still missing an “end sub” , plus the entire part that is supposed to set the Thin innerBorders…

Hello @Kitfox,

Please try if you can use the following macro instead:

Sub cellRange_ThickThin( Optional strRange )
REM Set the Outer Border of a cellRange within the Active Sheet to Thick, and the inside cell borders to Thin.
REM <strRange> : [OPTIONAL] String representing the address of the cellRange whose borders to set.
REM				 Leave empty for the current Selection.
REM Example call: cellRange_ThickThin( "D5:F15" ); ''Or for the current Selection: cellRange_ThickThin().
	Dim oRange As Object
	If IsMissing( strRange ) Then
		oRange = ThisComponent.CurrentSelection
	Else
		oRange = ThisComponent.CurrentController.ActiveSheet.getCellRangebyName( strRange )
	End If

	Dim aThinBorder As New com.sun.star.table.BorderLine2
	aThinBorder = oRange.TopBorder2
	aThinBorder.Color          = 0
	aThinBorder.InnerLineWidth = 0
	aThinBorder.OuterLineWidth = 1
	aThinBorder.LineDistance   = 0
	aThinBorder.LineStyle      = 0
	aThinBorder.LineWidth      = 1
	
	oRange.TopBorder2    = aThinBorder
	oRange.RightBorder2  = aThinBorder
	oRange.BottomBorder2 = aThinBorder
	oRange.LeftBorder2   = aThinBorder
	
	Dim aTableBorder As New com.sun.star.table.TableBorder2
	aTableBorder = oRange.TableBorder2
	
	Dim aThickBorder As New com.sun.star.table.BorderLine2
	aThickBorder = aTableBorder.TopLine
	aThickBorder.Color          = 0
	aThickBorder.InnerLineWidth = 0
	aThickBorder.OuterLineWidth = 71
	aThickBorder.LineDistance   = 0
	aThickBorder.LineStyle      = 0
	aThickBorder.LineWidth      = 71
	
	aTableBorder.TopLine = aThickBorder
	aTableBorder.RightLine = aThickBorder
	aTableBorder.BottomLine = aThickBorder
	aTableBorder.LeftLine = aThickBorder
	
	oRange.TableBorder2 = aTableBorder
End Sub

HTH, lib

Perfect!!! Works great!

Thanks for the help!!!

Good job @Kitfox :slight_smile:

To mark the above answer as correct, please click on the checkmark icon on the left of the answer, and karma-permitting upvote it.

With Regards,
lib

Moved to new post