Disabling row and column headers option

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way in VBA to disable the "Row and column headers" option within
the Tools | Options... | View tab menu so that a user cannot check (or
uncheck) the checkbox? I would like to do this without having to create a
custom toolbar or menu.

Thanks in advance for any guidance.
 
I don't think you can change any of those built-in dialogs.

You can disable them, but you can't change them.
 
Dave,

What I want to do is click on the "Row and column headers" checkbox so that
the row and column headers are not displayed. Then I want to prevent users
from re-displaying the row and column headers (i.e., unchecking the checkbox).

Can you tell me how to disable the "Row and column headers" checkbox in VBA?

Thanks,
Bob
 
Nope. I can't.

You don't have that kind of granular control over the dialogs in excel.

In my USA version of Excel, I could use a line like this:

With Application.CommandBars("worksheet menu bar").Controls("Tools")
'.enabled or .visible????
.Controls("Options...").Enabled = False
End With

to try to prevent users from clicking on Tools|Options...

But if they reset the toolbar or disabled macros, then it would not work very
well!
 
Dave,
As a second option, I guess I could do something like:

Private Sub Worksheet_Activate()
ActiveWindow.DisplayHeadings = False
End Sub

Thanks again for all your help and advice.
Bob
 
You could use that, but the user could disable macros or events or just change
the option.


Dave,
As a second option, I guess I could do something like:

Private Sub Worksheet_Activate()
ActiveWindow.DisplayHeadings = False
End Sub

Thanks again for all your help and advice.
Bob
Click to expand...
 
It might be a bit of overkill, but if you did the DisplayHeadings = False and
then
deleted the Tools>Options from the menu, would it serve your purpose. Of
course, you would have to put it back at the end of the macro, because it
would otherwise carry over to any subsequent instance of Excel. But it would
make it difficult for a novice to restore the headings, since the check box
would be inaccessible untile the menu item was restored.

Bob said:
Dave,
As a second option, I guess I could do something like:

Private Sub Worksheet_Activate()
ActiveWindow.DisplayHeadings = False
End Sub

Thanks again for all your help and advice.
Bob
Click to expand...
 
Basically, that's what Dave's code does. But he didn't mention restoring it
before closing the program.

Bob said:
Dave,
As a second option, I guess I could do something like:

Private Sub Worksheet_Activate()
ActiveWindow.DisplayHeadings = False
End Sub

Thanks again for all your help and advice.
Bob
Click to expand...
 
Back
Top