setting gridlines color without activating the worksheet

[lalbatros]

Hello,

As a consequence of another thread, I have now this new question.
(see http://www.mrexcel.com/forum/showthread.php?p=1605723)

I would like to write a macro that sets the gridlines color of several worksheets.
But I don't want to activate each worksheet in a loop because of the visual impact.

It seem this is not possible since GridlineColorIndex is a property of a Window, not a property of a worksheet.
And to get a handle on a window, you need to activate a worksheet ...

Would there be a possibility anyway ?

thanks

 

[MorganO]

There is no need to activate a window to apply this property.

For instance you can just use:

Windows("Book2").GridlineColorIndex = 7

To change the gridline color and not activate the window.

Hope this helps.

Owen

 

[lalbatros]

MorganO,

The problem arises when you have several worsheets in a workbook.
Say you have three worksheet in a workbook: Sheet1, Sheet2, and Sheet3.
Assume you want to write a macro to set the gridlines for Sheet1 and Sheet2.
Would that be possible without activating these two sheets?
In addition, in this statement:

Windows("Book2").GridlineColorIndex = 7

only the gridlines from the active sheet in book2 are changed.
The other sheets are unchanged.

 

[MorganO]

I realize that I misunderstood your question when I first read it, and yes you are correct, there doesn't seem to be a direct method to set the gridlinecolorindex without first activating a workbook then setting the index.

I thought that the following would possibly work:

Application.Workbooks("Book2").Worksheets("Sheet2").Application.Windows("Book2").GridlineColorIndex = 5

and although it executed with no error, it wouldn't change the colorindex unless the sheet2 is the currently active sheet.

I am assuming this must be doing this because of the design of the Excel Object Model.

I am sorry that I cannot assist further.

Can an MVP share any insight into this issue?

Owen

 

[GlennUK]

I don't want to activate each worksheet in a loop because of the visual impact.

Do activate each worksheet if necessary, just use Application.ScreenUpdating = False before you start the loop.