VBA - Window given a worksheet

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

Hi
How can I get hold of the Window object, given a Worksheet object? I have a
worksheet and want to set the DisplayGridlines property.

Thanks
Ben
 
Hi Ben!

First let's get the gridlines code. Try recording:

Tools > Options > View Tab
Place or remove check in Gridlines
OK

Here's what I got:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 19-02-2004 by Norman Harker
'

'
ActiveWindow.DisplayGridlines = False
End Sub

So you can use:

Sub Test()
Application.Worksheets("Sheet2").Select
ActiveWindow.DisplayGridlines = False
End Sub

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Norman

Yes I can do that, but I have a Worksheet object, it seems silly to have to
select it to be able to modify it. What if I want to keep the current
selection? If the app is visible, the user can affect the selection, which
may cause problems.

I was thinking, there should be something along the lines of -
worksheetObject.Window.Displaygridlines = False - but I can't get the window
for the worksheet.
 
Hi Ben!

I suppose the reason is that any worksheet might be active when you
run the subroutine. Remember that this is a setting which varies from
sheet to sheet.

If you know the sheet that is active and you want to change gridlines
you can use:

Sub Test2()
ActiveWindow.DisplayGridlines = False
End Sub

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
I don't think I've explained myself too well... I've got an excel object in
a stand alone VB app.
I have a worksheet object, as in....
Dim worksheetObject as Excel.Worksheet
Set worksheetObject = blah de blah

The worksheet object gets passed around from function to subroutine etc.
etc.

Now, I DON'T want to select the worksheet or it's window, because the user
may be doing something on another worksheet. It is possible to get hold of
ranges and charts without doing a .Select. Is it possible to get hold of
the window for the worksheet in the same manner?
 
Hi Ben!

Does the following help:

Sub test()
ActiveWorkbook.Windows(1).DisplayGridlines = False
End Sub

The active workbook can have more than one window so I suppose you
must specify which (or a) window before you can change the gridlines
setting.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top