https://lichess.org/ has a Zen mode. Can I create a Zen mode in Excel to celebrate Spreadsheet Day?

 

Zen Concept

Distractions are bad in 3 minute blitz chess games. I use Zen mode to focus on the game.

Excel has many great features but at times I hide them to focus on the data. Can I create a Zen mode?

 

Excel File

Download my Excel file (has vba code). Make sure you turn features back on before closing!

 

Features to Disable

There are so many but I focused on these:

• formula bar
• gridlines
• headings (rows & columns)
• autocorrect
• F1 help
• ribbon
• status bar

 

Manual or Automated?

Is it better to temporarily disable features manually or automatically (vba) ?

Manual approach is simpler but takes more time. I couldn’t resist playing with vba to automate it!

 

Checkboxes

1)Check or uncheck the checkboxes. 2)Press button ‘Click here to apply zen settings’.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Checked = true, unchecked = false. Column A stores the values. VBA loads the true/false values.

 

Zen Mode Look

A sheet with text looks like this:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Rare to have a lot of text in Excel but it can happen. Easier to read if you have more screen space.

Explore raw data without distractions:

 

 

 

 

 

 

 

 

 

 

 

A sheet with pivot tables and charts:

 

 

 

 

 

 

 

 

 

VBA code

I wrote some of the code and googled to find other bits. I tinker around with vba, I’m not a programmer, so it took me a quite a while to get everything working.

 

Gridlines Off/On Toggle

Variable gridlines retrieves true/false value from cell A6 in sheet zen.

Sub zenGRIDLINES2()
‘GET THE TRUE/FALSE VALUE FROM A CELL
Dim gridlines As Boolean
gridlines = Range(“zen!A6”).Value
ActiveWindow.DisplayGridlines = gridlines
End Sub

 

Headings Off/On Toggle

I added vba code to loop through all sheets as by default it only affects current sheet:

Sub zenHEADINGS()
‘GET THE TRUE/FALSE VALUE FROM A CELL
Dim headings As Boolean
headings = Range(“zen!A6”).Value
For Each wsSheet In ThisWorkbook.Worksheets
wsSheet.Activate
With ActiveWindow
.DisplayHeadings = headings
End With
‘End If
Next wsSheet
End Sub

UPDATE: thanks to Daniel Choi for this compact vba to hide headlines:

Sub zenHEADINGS()
Dim headings As Boolean
headings = Range(“zen!A6”).Value
ThisWorkbook.Worksheets.Select
ActiveWindow.DisplayHeadings = headings
End Sub

 

Disable F1 Key

Mr Excel (video & post) speaks with financial modelers who remove F1 key from keyboard.

 

 

 

 

 

 

 

 

 

 

 

 

Why? Auditing formulas requires keys F2 and Esc. Accidentally pressing F1, located between them, wastes time (help menu opens slowly).

 

But…why not just disable F1 key with vba?

Sub zendisableF1()
Dim zhelp As Boolean, helpoff As String, helpon As String
zhelp = Range(“zen!A9”).Value
If zhelp = False Then Application.OnKey “{F1}”, “” Else Application.OnKey “{F1}”

End Sub

 

Financial modelers could reassign F1 key to do something more useful. Any ideas?

If you don’t want to accidentally toggle F1 back on use this code to turn it off:

Sub disableF1()
Application.OnKey “{F1}”, “”
End Sub

 

Examine the other procedures in my file. Code below is what’s attached to the button:

Sub Button6_Click()
Application.ScreenUpdating = False
Call zenHEADINGS
Call zenFORMULABAR
Call zenGRIDLINES
Call zenAUTOCORRECT1
Call zendisableF1
Call zenRibbon_ShowHide
Call zenstatus_bar
Application.ScreenUpdating = True
Sheet8.Activate
End Sub

I prefer to get all independent parts working and then call them in a final procedure.

 

VBA sources

When I know what I want to do but struggle with the syntax I google it. Here are sources I used:

• ‘https://www.exceltrainingvideos.com/hide-show-ribbon-in-excel-using-vba/
• ‘https://stackoverflow.com/questions/50726983/macro-to-show-hide-status-bar-in-excel
• ‘https://stackoverflow.com/questions/19019546/vba-minimize-ribbon-in-excel
• ‘https://www.automateexcel.com/vba/hide-worksheet-tabs/
• ‘https://www.pcreview.co.uk/threads/turn-off-autocorrect-in-excel-via-vba.3274233/
• ‘https://www.extendoffice.com/documents/excel/4344-excel-hide-headings-all-sheets.html

 

Cell or named range?

I could’ve added named ranges to cells A5:A11. It would allow you to insert/delete rows above or below without affecting the code. I was too lazy and added text DO NOT INSERT ROWS OR COLUMNS IN THIS SHEET.

 

Spreadsheet Day

Debra Dalgleish started spreadsheet day in 2010. It’s always fun to celebrate! I decided to practice vba. The ‘zen’ idea was fun to explore. However… if something goes wrong with the code it would cause many to panic (very un-zen).

 

Spreadsheet Day 2019

 

 

 

 

 

Two years ago I had one of my craziest ideas: an Excel Beer game to celebrate Spreadsheet Day. Yes it’s lame but it was fun to create and a great way to practice VBA. It makes my zen idea look rather lame in comparison.

 

About Me

 

 

 

 

I’m a data analyst. I live in Markham Ontario Canada. Most of my experience is with Excel and SQL. I’ve now learned a decent amount of Power BI (M and DAX).