Hide Gridlines in worksheets

Question

Hi,

Is it possible to hide gridlines in worksheets without the need to activate the worksheet?

Mike, who was very helpful, adviced that I should try using the following (my code in c#):

//get worksheet called summary and activate it
this.defaultWorksheet = (Excel.Worksheet)thisWorkbook.Worksheets["Summary"];
this.defaultWorksheet.Activate();

//hide the gridline for the current active window
this.thisApplication.ActiveWindow.DisplayGridlines=false;

The above works great but the problem is that I will need to activate each worksheet first before hiding the gridlines, which will slow down the workbook considerably. Rather than coding it like the above, is it possible to do something simialr to this?:

this.defaultWorksheet = (Excel.Worksheet)thisWorkbook.Worksheets["Summary"];
this.defaultWorksheet.DisplayGridLines=false;

I have tried to look in the worksheet object but haven't been able to find anything equivalent. It seems that only the 'ActiveWindow' object has the 'DisplayGridlines' property.

Appologise for this long post.

Thank you very much for your help in advance!

Sue-Ann

Answer 1

Unfortunately, the setting you're looking for is a member of the Excel.Window object, and not something associated with the actual Worksheet object. However, there may be a couple of different ways you can get the behavior you're looking for.

If you want the gridlines to be hidden on a worksheet that exists at design time, then the simplest method of doing this is to turn the gridlines off for that worksheet in the designer. Bring up the designer for the worksheet in VS, and the menus for Excel should be merged with the VS menu. Select Tools->Microsoft Office Excel Tools->Options... and turn off gridlines for the current window. This setting will persist when you run the application. Note that in the Options... dialog, the gridline option is listed under the "Window" settings, which is why the setting is associated with the Window and not the Worksheet itself. I don't know why this isn't exposed as a property on the Worksheet object, though - seems like it should be there.

If the worksheet has been dynamically added, this won't work, though. Or, if you sometimes want gridlines on, sometimes off, you'll again have to do something else. One good option is to turn them off for the current ActiveWindow in the SheetActivate event of the Workbook object. You're likely to see the gridlines for a split second, as the window is shown before the gridlines are removed, but other than that it should work fine.

So, if you can set the gridlines to be off in the designer, you'll never have to do the change programatically and everything will look like you want it. But, if you can't use the designer, then you're going to have to do something with the events. There are several events you can use - the SheetActivate event of the Workbook object is probably your best bet, though.

Sorry for the slow reply on this - we've been busy and I haven't been keeping as up to date on these forums as I'd like to be.

Cheers,
  Mike Whalen
Software Design Engineer - Visual Studio Tools for Office

Answer 2

Sue-Ann,

One way I have always done it is to loop thru the workbook using this: 

DirectCast(xlWorkBook.Worksheets(k), Excel.Worksheet).PageSetup.Application.ActiveWindow.DisplayGridlines = False. Where each time k will increase by 1 until you reach the number of pages you want.