PDA

View Full Version : Drawing Borders in VBA



Saladsamurai
12-03-2009, 06:31 AM
So I have a range defined and named "TableRange"

I want to put inside borders and an outer border of medium thickness. I recorded a Macro and yanked the code. I cleaned it up a bit, but it still seems like overkill.

Is there anything else I can delete here to clean up my code a bit? Can this be condensed into 1 "With" statement?

If not, no big deal. But, it's an eyesore.

With TableRange.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With TableRange.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With TableRange.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With TableRange.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With TableRange.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With TableRange.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

Saladsamurai
12-03-2009, 06:44 AM
Woo hoo! This is promising:

TableRange.BorderAround xlContinuous, xlMedium

EDIT: Even better :)

TableRange.BorderAround xlContinuous, xlMedium
TableRange.Borders.Weight = xlMedium

Completely replaces

With TableRange.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With TableRange.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With TableRange.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With TableRange.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With


With TableRange.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With TableRange.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With



End With

Bob Phillips
12-03-2009, 06:52 AM
Can't see how that adds the inside borders.

mdmackillop
12-03-2009, 07:09 AM
Either

Selection.Borders.Weight = xlMedium


or

With Selection.Borders
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

Saladsamurai
12-03-2009, 07:14 AM
Can't see how that adds the inside borders.

Hey xld :)

The line TableRange.Borders.Weight = xlMedium

Takes care of it. I was skeptical, but then I tried it and it worked.

Now out of curiosity, is there a way two combine the two lines

Cells(NumScenario + 15, 8) = "Start"
Cells(NumScenario + 15, 8).Font.Bold = True

Into 1 single line? I know I am nitpicking here. Sorry

Bob Phillips
12-03-2009, 07:28 AM
Noi it is setting separte properties. You could : delimit them so they go on one line, but itr is still two statements.

Bob Phillips
12-03-2009, 07:31 AM
Hey xld :)

The line TableRange.Borders.Weight = xlMedium

Takes care of it. I was skeptical, but then I tried it and it worked.

That is interesting. I use Borderaround many times, but I then set the inside borders separately. Will be useful, thanks.