Protect the hided rows and colums headers view

[Villalobos]

Hello,

Now I use the below mentioned code to hide the row and colum headers, but I can not protect it. I mean that how should modify the code to avoid that if somebody want to see the heading just click Tools | Options, View. Check "Rows and columns headers" ? How do I turn off via VBA this option?

Private Sub hideHeadings()
Dim wrkbk As Workbook
Dim wrksh As Worksheet
Dim prev As Window

Set prev = ActiveWindow

For Each wrkbk In Workbooks
For Each wrksh In wrkbk.Worksheets
wrksh.Activate
ActiveWindow.DisplayHeadings = False
Next wrksh
Next wrkbk

prev.Activate

End Sub

Thanks in advance the reply!

 

[lohithsriram]

Hi Villalobos,

You can have the below code under all the worksheets Selection_change event . Even if any user enables the Headings, it will get hidden before they do any action on the worksheet. Then You can protect your VBA code with a password for someone to change it.

There should be a better way to do this. You will get a better replies. Till then, you can rely on this.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveWindow.DisplayHeadings = True Then ActiveWindow.DisplayHeadings = False
End Sub

 

[Villalobos]

Hi Lohith,

Thank you for your attention! I put your code into Module1, but my problem still open. If I go to Option - View I can turn on Rows and Columns headers by one right click. Where I made mistake?

 

[SirJB7]

Private Sub Worksheet_SelectionChange (ByVal Target As Range)

Hi, Villalobos!
The code goes in the related worksheet class module and not in a standard module.
Regards!

 

[Villalobos]

Hi SirJB7,

Yes, you are right, thank you for that!

@Lohith
Thank you the code!

 

[SirJB7]

Hi, Villalobos!
Glad you solved it, all credit to lohithsriram. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!