Borders on Single Columns or Single Rows

I found a bug this morning (my bug, not Microsoft’s). I have a macro that creates the illusion of gridlines for when I fill the interior of a range. It does so by adding gray borders everywhere.

Before After

The old macro looked like this

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

However, this morning I tried to apply this macro to a single column range. I guess that is the first time I’ve tried to do that, because I got an error. Borders does not have an xlInsideVertical item if there is only one column. That makes sense. I fixed it by testing the row and column counts.

Private Sub cmdGridlines_Click()
   
    With Selection
        Selection.BorderAround xlContinuous, xlThin, , RGB(192, 192, 192)
        If .Columns.Count > 1 Then
            With .Borders(xlInsideVertical)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .Color = RGB(192, 192, 192)
            End With
        End If
        If .Rows.Count > 1 Then
            With .Borders(xlInsideHorizontal)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .Color = RGB(192, 192, 192)
            End With
        End If
    End With
   
End Sub
Posted in Uncategorized

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. 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

  7. 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
  8. 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

  9. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *