Solved

Change Excel default to show no gridlines

Posted on 2013-12-29
18
7,092 Views
Last Modified: 2016-05-25
Hello,

Is there a way to change the default view of newly launched Excel 2013 spreadsheets so that no gridlines are present?

Thanks
0
Comment
Question by:Steve_Brady
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +2
18 Comments
 
LVL 54

Assisted Solution

by:Joe Winograd, EE MVE 2015&2016
Joe Winograd, EE MVE 2015&2016 earned 100 total points
ID: 39745233
Hi Steve,

There's no option I'm aware of that would set the default view to no gridlines. The good news is that it's just two mouse clicks in Excel 2013 – click View then un-check Gridlines.

I haven't tried this, but I'm sure you could make a custom template with no gridlines and then when you do File>New, select that custom template. Regards, Joe
0
 
LVL 81

Accepted Solution

by:
byundt earned 400 total points
ID: 39745576
Further to Joe Winograd's suggestion:
Create a new workbook with a single worksheet and turn off gridline display. Save the workbook in your XLSTART folder, and name it Book.XLT  Save it again in the same folder as Sheet.XLT

These workbooks will be used as the templates for new workbooks, and for worksheets added to existing workbooks. Any formatting that you do on them (such as not displaying gridlines) will be inherited by the new workbooks or newly added worksheets.

In my computer (Windows 7 with Excel 2013), the XLSTART folder is in C:\Users\Admin\AppData\Roaming\Microsoft\Excel\XLSTART
0
 
LVL 81

Expert Comment

by:byundt
ID: 39745577
If you want to hide the gridlines on existing workbooks that are opened in Excel, you will need to use Application level events to trap workbooks being opened, then turn the gridlines off in every worksheet in those workbooks. In other words, you will need a VBA macro.

I'm hoping that isn't what you were requesting. But if it is, I'll program a macro for you tomorrow if someone hasn't beat me to it.

Brad
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 81

Assisted Solution

by:byundt
byundt earned 400 total points
ID: 39746192
I've attached a workbook that will turn off gridline display for all workbooks that are subsequently opened. Code in that workbook uses application events to trap the opening of a new (or previously saved) workbook.

If you put the workbook in your XLSTART folder, it will then open automatically when you launch Excel.
'All of this code must go in ThisWorkbook code pane. It won't work at all if installed anywhere else!

Public WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
Dim ws As Worksheet
Dim wn As Window
Dim celHome As Range
Set celHome = ActiveCell
Application.ScreenUpdating = False
For Each ws In Wb.Worksheets
    If Wb.Windows(1).Visible = True Then
        ws.Select
        Set wn = ActiveWindow
        wn.DisplayGridlines = False
    End If
Next
Application.Goto celHome
End Sub

Private Sub Workbook_Open()
Instantiate
ThisWorkbook.Windows(1).Visible = False
End Sub

Open in new window


'This code goes in a regular module
Sub Instantiate()
Set ThisWorkbook.App = Application   'Instantiate application level events
End Sub

Open in new window

I've attached a sample workbook that contains the suggested code. It opens in a hidden window. Any workbook that opens after it will have gridline display hidden. Any workbook that is already open will continue to display gridlines (or not) depending on its previous gridline display property.
ApplicationEventsQ28327226.xlsm
0
 
LVL 33

Expert Comment

by:jppinto
ID: 39748476
What about turning the default gridlines color to white, the same as the workbooks background color? This way the gridlines would not show...

This can be done on Excel 2010 by going to File-Options. On the Advanced tab, scroll down until you see on the "Display options for this worksheet", the option for Gridline color. Just change it to white.

Hope it helps.

jppinto
0
 

Author Comment

by:Steve_Brady
ID: 39784113
Thanks for the helpful feedback—and especially for the specifics re saving a template, etc.
0
 

Author Comment

by:Steve_Brady
ID: 39784120
Oops. I meant for the last 100 points to go in the post which contains the DBA and attached sample file. I'm not sure how to fix it after the fact but at least it got to the same expert.

I suppose it's not a big deal but at least to have it correct for future reference, I will ask an EE support person to look at it.
0
 
LVL 54

Expert Comment

by:Joe Winograd, EE MVE 2015&2016
ID: 39784136
Steve,
Just under your question in the upper right is a link that says Request Attention. Click that and request that the question be re-opened, then you'll be able to re-assign the points as you'd like. Regards, Joe
0
 

Author Closing Comment

by:Steve_Brady
ID: 39791675
Thanks for the assistance.
0
 

Expert Comment

by:AiaXX
ID: 41592285
Brad (byundt),

When I tried saving your Visual Basic attached sheet to my XLSTART folder and then open a new Excel spreadsheet I receive an error message.

Error message reads:
"Microsoft Visual Basic, Run-time error '5': Invalid procedure call or argument"

When I select the "debug" button I am taken to your code with the following like highlighted in yellow:

"Application.Goto celHome"

Hoping you could shed light on a fix for this. Thank you kindly.

Aia
0
 
LVL 81

Expert Comment

by:byundt
ID: 41592295
Aia,
Could you post the workbook that causes the problem?

Brad
0
 

Expert Comment

by:AiaXX
ID: 41592309
Hi Brad,

Sure, it's the exact same sheet that was attached to your comment above dated, 2013-12-30 at 07:10:11.

Attaching to this comment to ensure no confusion.

Thanks,
Aia
ApplicationEventsQ28327226.xlsm
0
 
LVL 81

Expert Comment

by:byundt
ID: 41592366
I can reproduce the error if ApplicationEvents is opened first. The fix is to sandwich the statement producing the error with error handling statements:
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
Dim ws As Worksheet
Dim wn As Window
Dim celHome As Range
Set celHome = ActiveCell
Application.ScreenUpdating = False
For Each ws In Wb.Worksheets
    If Wb.Windows(1).Visible = True Then
        ws.Select
        Set wn = ActiveWindow
        wn.DisplayGridlines = False
    End If
Next
On Error Resume Next
Application.Goto celHome
On Error GoTo 0
End Sub

Open in new window

0
 

Expert Comment

by:AiaXX
ID: 41592371
Thank you Brad,

Could you please attached a spreadsheet where you saved the changes, to ensure I don't make an error when I add the correction?

Much appreciated.
Thank you,
Aia
0
 
LVL 81

Expert Comment

by:byundt
ID: 41592550
0
 

Expert Comment

by:AiaXX
ID: 41617132
Thank you for uploading the template worksheet with the corrections to the VBA macro.

Unfortunately, after saving your latest spreadsheet to our XLSTART folder, the guidelines are still not defaulting to off. Did you find success in doing the same thing? Do you have any suggestions by chance, maybe something I'm doing incorrectly?

Thank you kindly,
Aia

Here are the steps I followed:

1) Downloaded your spreadsheet you uploaded 2016-05-12 19:50;47
2) Open XLSTART folder located here: C:\Users\[myusername]\AppData\Roaming\Microsoft\Excel\XLSTART
3) Copy the spreadsheet to this folder
4) Restarted computer
5) opened a new Excel Spreadsheet
6) Expected result: Gridlines are defaulted to "off", Actual Result: Gridlines were not defaulted to "off".
0
 
LVL 81

Expert Comment

by:byundt
ID: 41617143
Aia,
Please confirm that the spreadsheet actually got opened when you relaunched Excel. To do this:
1. ALT + F11 to open the VBA Editor
2. Look in the Project Explorer pane at the left. You should see ApplicationEventsQ28327226-1.xlsm listed, as well as any other workbook you opened.

If ApplicationEventsQ28327226-1.xlsm is opened, the next thing I want you to test is whether it is running its code when you open another workbook. To do this, you place a "Stop" in the code:
1. ALT + F11 to open the VBA Editor
2. Find sub App_WorkbookOpen, then click in the margin to the left of the following statement:
Set celHome = ActiveCell

Open in new window

Excel should respond by adding a large maroon dot in that margin
3. ALT + F11 to return to the worksheet user interface, then open another workbook. Control should return to the VBA Editor, with the Set celHome statement highlighted in yellow.

Brad
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question