14

I have a VBA macro over Excel 2013 which generate a separate excel report. In this excel report that is created, I would like to turn off the GridLines.

The only piece of code that I come across to make this happens is as below

ActiveWindow.DisplayGridlines = False

However, this excel is generated in the background i.e., 

Dim appObject As New Excel.Application
appObject.Visible = False

Which means that this report is not the ActiveWindow. Is there an alternate way of turning off the gridlines without using the ActiveWindow object?

tempidope
  • 823
  • 1
  • 12
  • 29
  • `ActiveWindow` and `appObject.ActiveWindow` are the same thing - you just don't need to qualify it within its own application object. – Comintern Nov 01 '16 at 20:56
  • 1
    As per [this](https://msdn.microsoft.com/en-us/library/office/ff835196.aspx), try `Windows("book1.xls").DisplayGridlines = False` . – OldUgly Nov 01 '16 at 20:58
  • Sorry guys, Windows("filename.xls") gives me subscript out of range. That XLS is not open because of the appObject.Visible=False if that makes any sense. – tempidope Nov 01 '16 at 21:07
  • 1
    Do you have a reference to the Workbook? – Comintern Nov 01 '16 at 21:08
  • @Comintern yes I do. I've got reference to the sheets, workbook and the application object. – tempidope Nov 01 '16 at 21:10

4 Answers4

17

If you have a reference to the workbook, you can just iterate over all of the Windows in its collection. If the application isn't visible, you should only get 1 but it's safer than trying to hard code an index:

Private Sub ToggleGridLines(target As Workbook)
    Dim wnd As Window
    For Each wnd In target.Windows
        wnd.DisplayGridlines = False
    Next
End Sub

Note that this will set change the display on the active worksheet in the workbook - why this is a property of the window and not the worksheet is beyond me.

EDIT:

Thanks to the link that @Tim shared, I realized I'd completely spaced off the SheetViews collection. This should turn off gridlines for an arbitrary Worksheet object:

Private Sub TurnOffGridLines(target As Worksheet)
    Dim view As WorksheetView
    For Each view In target.Parent.Windows(1).SheetViews
        If view.Sheet.Name = target.Name Then
            view.DisplayGridlines = False
            Exit Sub
        End If
    Next
End Sub
Comintern
  • 21,855
  • 5
  • 33
  • 80
  • Dem trickeh parameters! xD But seriously. The MSDN documentation mentions that Sheets is a child of Windows. [Mr. Excel](http://www.mrexcel.com/forum/excel-questions/320215-worksheetview-object.html) has a nice demo of what good can come of two windows for the same workbook. I'm trying to wrap my head around it more. – Tim Nov 01 '16 at 21:50
  • @Tim - Interesting link. I deal with `Excel.Windows` so rarely I completely forgot about the `SheetViews` collection. See the edit. – Comintern Nov 01 '16 at 22:25
  • @Comintern fantastic, your solution worked. Really weird how the hierarchy is - however did solve my problem. – tempidope Nov 02 '16 at 15:02
2

The ActiveWindow is a member of the Windows objects collection. As with any collection, simply refer to the actual window by name rather than by specifying the active window. eg

Windows("My Workbook.xls").DisplayGridlines = False
Tim
  • 2,701
  • 3
  • 26
  • 47
  • Sorry @Tim, subscript out of range. That XLS is not open because of the appObject.Visible=False if that makes any sense. – tempidope Nov 01 '16 at 21:07
  • I'd need to see more code. So far the only code dim's an object then sets it invisible. Dimming it is not the same as creating it. Comintern just posted some code for looping through all the windows but it will fail until a workbook is actually created (which we would see in more of your code). – Tim Nov 01 '16 at 21:20
  • 1
    @Tim - Thus the Workbook as a parameter. ;-) – Comintern Nov 01 '16 at 21:21
0

We can either do it as "Comintern" suggested or activating the wanted sheets to execute de adequate line of code in a loop. I tried the code posted above in several ways and I'm posting the snippet that worked the best for me:

Sub GridLines(Optional target As Worksheet, Optional display As Boolean = True)

    Dim oWnd As Window
    Dim oShView As WorksheetView
    If IsMissing(target) Or target Is Nothing Then
        For Each oShView In ActiveSheet.Parent.Windows(1).SheetViews
            oShView.DisplayGridlines = display
        Next
    Else
        For Each oShView In target.Parent.Windows(1).SheetViews
            If oShView.Sheet.Name = target.Name Then
                oShView.DisplayGridlines = display
                Exit For
            End If
        Next
    End If
    Set oShView = Nothing
    Set oWnd = Nothing
End Sub

Any feedback is very welcomed

João Batista
  • 1
  • 1
0

Why use a single line of code when you can use 20?

Windows(ActiveWorkbook.Name).DisplayGridlines = False
user1636620
  • 13
  • 4