How to stop the user for turning on gridlines & headings

mcomp72

Active Member
Joined
Aug 14, 2016
Messages
275
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2011
Platform
  1. Windows
  2. MacOS
For the workbook I am creating, I am turning off gridlines & headings. However, the user can easily turn them back on by going to the View tab and clicking the check boxes for those two items (I'm using Excel 2016). Is there a way to make it so they CANNOT turn them back on?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
For the workbook I am creating, I am turning off gridlines & headings. However, the user can easily turn them back on by going to the View tab and clicking the check boxes for those two items (I'm using Excel 2016). Is there a way to make it so they CANNOT turn them back on?

I am not aware of a way to prevent users from turning on or off those settings even if you Protect the worksheet and/or workbook however, if you can use vba then you could try something along these lines :

Code in the ThisWorkbook Module :
Code:
Option Explicit

Private WithEvents oCmbrsEvents As CommandBars


Private Sub Workbook_Activate()
     Set oCmbrsEvents = Application.CommandBars
     Call oCmbrsEvents_OnUpdate
End Sub


Private Sub oCmbrsEvents_OnUpdate()
    If ActiveWorkbook Is Me Then
        If ActiveSheet Is Sheet1 Then
            With Application.ActiveWindow
                If .DisplayGridlines Or .DisplayHeadings Then
                    .DisplayGridlines = False
                    .DisplayHeadings = False
                End If
            End With
        End If
    End If
    With Application.CommandBars.FindControl(ID:=2020): .Enabled = Not .Enabled: End With
End Sub

The above works for Sheet1 of the workbook that contains the code.

Remove the following code lines :
If ActiveWorkbook Is Me Then
If ActiveSheet Is Sheet1 Then

To make it work for all sheets in all workbooks.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

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
Back
Top