We help IT Professionals succeed at work.

Change Excel default to show no gridlines

Steve_Brady
Steve_Brady asked
on
14,853 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
Comment
Watch Question

Joe WinogradDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Mechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

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

Author

Commented:
Thanks for the helpful feedback—and especially for the specifics re saving a template, etc.

Author

Commented:
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.
Joe WinogradDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2018

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

Author

Commented:
Thanks for the assistance.

Commented:
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
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
Aia,
Could you post the workbook that causes the problem?

Brad

Commented:
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
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

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

Commented:
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
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:

Commented:
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".
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.