11 thoughts on “Borders on Single Columns or Single Rows”

1. Just a minor point, but you could remove “Selection” from the second line of your macro, so it reads

   With Selection
      .BorderAround xlContinuous, xlThin, , RGB(192, 192, 192)

2. This is the perfect place to handle the error rather than trying to avoid it. Do this “test if a property would make sense” with code to format a chart and the code would get very unwieldy. On the other hand, stick in a ‘On Error Resume Next’ at the top and let the code run. Properties that don’t apply to a particular chart will be gracefully skipped w/o any fancy or overly complicated tests.

3. JP: Quite right. There’s some insight into how this macro was written. It started as a one-liner, but then I realized I needed more than one line, so I created the With block. But, of course, I forgot to remove the reference from that first line.

   Tushar: On Error seems a little much for this particular situation. Aren’t you afraid it will mask other errors?

4. Dick wrote “Tushar: On Error seems a little much for this particular situation. Aren’t you afraid it will mask other errors?”

   When something is the more straightforward way, it cannot possibly be “a little much.” {grin}

   Yes, masking unexpected errors is a possibility but if you set the scope of the error handler to be as narrow as possible, the risk is minimal.

   In the case of the chart formatting code, there was one instance where with a new version of Excel the chart did not format as expected. It turned out Microsoft changed something so that my code was no longer valid and the error handler was masking the error. But, it was once in…what…18…20 years or so.

   By contrast, if I tried to handle the options w/o an error handler, I would have to deal with present/absent primary/secondary category/value axis, legend, axis titles, chart title, horizontal/vertical gridlines, axis labels, data labels, etc., etc., etc.

   The error handler version of the code is a lot cleaner. And, cleaner code is easier to debug, understand, and maintain.

5. Years back, the Visual Basic Programmers Journal (VBPJ), in an April 1st edition editorial, stated that the next version of VB would have a Global “On Error Resume Next” statement included in the language.

   Maybe, Microsoft should have considered that option before releasing Office 2007.

6. Dick: You could also create a style with this coloring set up and just apply the style to the range.

7. What about this simpler code that seems to work for all selection situations…

   Sub cmdGridlines_Click()
   With Selection
   .BorderAround xlContinuous, xlThin, , RGB(192, 192, 192)
   With .Borders
   .LineStyle = xlContinuous
   .Weight = xlThin
   .Color = RGB(192, 192, 192)
   End With
   End With
   End Sub

8. Sorry, I forgot about the

    and

   tags…

   Sub cmdGridlines_Click()
       With Selection
           .BorderAround xlContinuous, xlThin, , RGB(192, 192, 192)
           With .Borders
               .LineStyle = xlContinuous
               .Weight = xlThin
               .Color = RGB(192, 192, 192)
           End With
       End With
   End Sub

9. For Automatic color borders

   Sub AllBorders()
   Selection.Borders.LineStyle = xlContinuous
   End Sub

   For Color borders

   Sub AllBorders()
   With Selection.Borders
   .LineStyle = xlContinuous
   .Color = RGB(192, 192, 192)
   End With
   End Sub

10. Very good Sam, you are right… the BorderAround property does not have to be set… setting just the Borders property takes care of those borders as well.

11. Thanks for all of the great/clean code. I seem to recall this not being required in Excel 5, when changing the background color – borders remained.
    Billy Gee

Posting code? Use <pre> tags for VBA and <code> tags for inline.