setting gridlines color without activating the worksheet

lalbatros

Well-known Member
Joined
Sep 5, 2007
Messages
659
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

MorganO

Active Member
Joined
Nov 21, 2006
Messages
483
There is no need to activate a window to apply this property.

For instance you can just use:

Code:
Windows("Book2").GridlineColorIndex = 7

To change the gridline color and not activate the window.

Hope this helps.

Owen
 

lalbatros

Well-known Member
Joined
Sep 5, 2007
Messages
659
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:

Code:
Windows("Book2").GridlineColorIndex = 7

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

MorganO

Active Member
Joined
Nov 21, 2006
Messages
483
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:

Code:
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,531
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,962
Messages
5,599,065
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top