Skip to main content

VBA Procedure to apply borders to a excel range of cells

The following VBA procedure can be used to apply borders to a range of cells

Public Sub ApplyBorder(ToRange As Range, intColorIndex As Integer)
     On Error Resume Next
     With ToRange
        With .Borders(xlDiagonalDown)
            .LineStyle = xlNone
        End With
        With .Borders(xlDiagonalUp)
            .LineStyle = xlNone
        End With
       
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = intColorIndex
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = intColorIndex
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = intColorIndex
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = intColorIndex
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = intColorIndex
        End With
    End With
End Sub
Labels:

Comments

  1. UnknownMarch 10, 2014 at 3:16 AM

    Thanks! Im kinda looking into VBA right now, but i am not quite sure how much time it would save me. In this example, how many boders/worksheets would it take so its worth using VBA instead of normal excel functions? Usually i use guides like http://www.excel-aid.com/excel-borders-drawing-borders-around-cells-and-cell-ranges.html to reproduce the steps, in this case it seems fairly easy to do. So what would you recommend for someone who mostly edits only a few sheets every day, most of them "unique"?

    ReplyDelete
  2. hiteshgoldeneyeMarch 10, 2014 at 3:53 AM

    Hi Markus,

    Thanks for your comment, you normally wouldnt bother doing this using VBA unless you have a lot of worksheets to be formatted, however if you are generating some sheets through VBA code you might want the code to do the formatting too.

    ReplyDelete
  3. UnknownMarch 10, 2014 at 5:51 AM

    Thanks for the quick answer, i guess i will just keep doing it manually then. Untill i have a ton of sheets at least. So i hopefully will be back for some VBA lessons one day :)

    ReplyDelete
  4. hiteshgoldeneyeMarch 10, 2014 at 6:22 AM

    hehe most wekcome, there is no rule of thumb or threshold for when you should automate, its more a gut feeling and a consideration about will the automated solution be reused multiple times (maybe with some small tweaks). For starting of with VBA i suggest go through some web search on the basics. Here is one good link to start with http://dailydoseofexcel.com/archives/2004/04/28/beginning-vba-navigating-the-object-model/. If you are stuck and need help on any issue i will be glad to help

    ReplyDelete

Post a Comment

Popular posts from this blog

A simple customization in MPOS (Blank Operation) with AX7

Hi All, I recently had to add a Blank Operation to Modern POS (MPOS) to open a url from MPOS. Blank Operation as you may already be aware enable you to extend Microsoft Dynamics Retail for POS by adding custom logic that can be triggered from the Retail POS Register buttons. The way to implement Blank Operations in MPOS  is different from Enterprise POS as MPOS is a modern app as compared to EPOS which is a windows forms based app. So lets explore a very simple customization i.e. we want to open a URL on triggering a button from MPOS. 1. We would need to start with AX to add a button to the layout of MPOS. If you do not want to disturb the standard layouts its better to copy one of the existing layouts and then modify it using the designer. Please note the designer only opens in Internet Explorer so it will save you time by not trying to open it in other browsers e.g Chrome 2. Next we need to add this layout to the Store where we intend to use it, in
Post a Comment
Read more

Hyper-V VM 100% disk utilization

I was facing a persistent issue of 100% disk utilization on starting any VM in my Hyper-V. The below link helped me to resolve it https://social.technet.microsoft.com/Forums/office/en-US/2b184f22-ea8c-4bc3-9f64-8b46eeaeef61/hyperv-host-disk-usage-at-100?forum=w8itprovirt
Post a Comment
Read more