CODEWITHSUNDEEP
excelms-accessvba
Bruno
Bruno

Reputation: 6459

Add gridlines to an Excel sheet

In the below code, how can I add grid-lines to the entire Excel sheet?

    Set objApp = CreateObject("Excel.Application")

    objApp.Visible = True
    Set wb = objApp.Workbooks.Open("template.xls", True, False)
    wb.Sheets(1).Rows(3).Delete
    wb.Sheets(1).Range("A1").Value = title
    'need to format column E & F as currency

    Set objApp = Nothing

Upvotes: 0

Views: 7667

Answers (2)

Patrick
Patrick

Reputation: 702

Try this:

ActiveWindow.DisplayGridlines = True

That should turn the gridlines on. And, of course, setting the property to False will turn them off.

Upvotes: 3

Stewbob
Stewbob

Reputation: 16899

This is the long answer (the code Excel VBA generates when you record a Macro). You can definitely shorten this up. For instance, you don't need to set the .ColorIndex or .TintAndShade properties just to do a standard black [edit] border.

Cells.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With

EDIT

For gridlines:

ActiveWindow.DisplayGridlines = True

you can also use:

Windows(1).DisplayGridlines = True

Upvotes: 4

Related Questions