Ask Your Question

Border Macro No Longer Works

asked 2018-02-10 02:05:39 +0200

Kitfox gravatar image

updated 2018-02-10 03:55:56 +0200

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("")

rem ----------------------------------------------------------------------
dim args1(7) as new
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())
edit retag flag offensive close merge delete


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.

librebel gravatar imagelibrebel ( 2018-02-10 02:37:06 +0200 )edit

I changed it as you suggested.

Kitfox gravatar imageKitfox ( 2018-02-10 03:56:31 +0200 )edit

Better already :) But i'm still missing an "end sub" , plus the entire part that is supposed to set the Thin innerBorders...

librebel gravatar imagelibrebel ( 2018-02-10 04:05:41 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-02-10 04:08:22 +0200

librebel gravatar image

updated 2018-02-10 04:11:10 +0200

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
        oRange = ThisComponent.CurrentController.ActiveSheet.getCellRangebyName( strRange )
    End If

    Dim aThinBorder As New
    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
    aTableBorder = oRange.TableBorder2

    Dim aThickBorder As New
    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

edit flag offensive delete link more


Perfect!!!! Works great!

Thanks for the help!!!

Kitfox gravatar imageKitfox ( 2018-02-10 04:48:50 +0200 )edit

Good job @Kitfox :)

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

librebel gravatar imagelibrebel ( 2018-02-10 14:18:23 +0200 )edit

Moved to new post

lonk gravatar imagelonk ( 2019-10-12 13:12:27 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-02-10 02:05:39 +0200

Seen: 174 times

Last updated: Feb 10 '18