Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Bordering Cells Macro (Excel 2002 SP2)

    I found the following macro posted on newsgroup. Can sombody tell me how you would modify this macro so that it will be applied to selected cells when more than one cell is selected? I would also like the border applied if the selected cell is a merged cell. And finally, but not least How you would remove the border? Clearing cell or Format->Cell->Border->None does not appear to work? <img src=/S/nosleep.gif border=0 alt=nosleep width=27 height=15>

    Sub CelluleArrondirBord()
    Set depart = ActiveCell
    r1 = depart.Height
    r2 = depart.Width
    r3 = depart.Top
    r4 = depart.Left
    ActiveSheet.Shapes.AddShape(msoShapeRoundedRectang le, _
    r4, r3, r2, r1).Select
    Selection.ShapeRange.Fill.Visible = msoFalse
    depart.Select

    End Sub

  2. #2
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bordering Cells Macro (Excel 2002 SP2)

    Here you go, the changes are in red
    <pre>Sub CelluleArrondirBord()

    <font color=red>Dim depart As Range
    Dim r1 As Single
    Dim r2 As Single
    Dim r3 As Single
    Dim r4 As Single
    Set depart = Selection</font color=red>
    r1 = depart.Height
    r2 = depart.Width
    r3 = depart.Top
    r4 = depart.Left
    ActiveSheet.Shapes.AddShape(msoShapeRoundedRectang le, _
    r4, r3, r2, r1).Select
    Selection.ShapeRange.Fill.Visible = msoFalse
    depart.Select
    End Sub
    </pre>


    The main change is to change set depart = ActiveCell to Set Depart = Selection.

    THis will work with a single cell or multiple cells.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bordering Cells Macro (Excel 2002 SP2)

    That macro is drawing a rectangle shape on top of the cell, it is not putting a border around the cell. Is that really what you want to do? If that is what you want to do, then I think someone else has modified your code to make it do what you want. To remove this, you would have to find the rectangle object and delete it.

    If what you want is really a border, then you could use the code below:

    <pre> Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    </pre>


    To remove a real border, you would use code like this:

    <pre> Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    </pre>

    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Jul 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bordering Cells Macro (Excel 2002 SP2)

    Thank you for your answers. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

    Legare, a special <img src=/S/kiss.gif border=0 alt=kiss width=34 height=15> .

    I'm a novice at VBA, but am learning a great deal with the help off this board. I resisted MS Office for a long time trying to hang on to my legacy apps. Excel has been a lot of fun trying to get everything in perspective. Still struggling a little with the mind set for Word. Having decided to put in the effort to master the program it is very nice to have such a resource. Again, thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •