Hi -
I currently have my sheet tabs hidden. What I'd like to do is: If a user attempts to unhide the tabs via the File | Options menu in Excel, for a macro to fire up and rehide the sheet tabs immediately. Nothing has to change on the active sheet, the only trigger being if the user simply attempts to unhide the sheet tabs. Any help/guidance is greatly appreciated. Thank you!
Jose
why dont' you password protect your workbook.
In the review tab, select protect workbook.
Thank you rabsofty. I was actually fearing that my post would be misunderstood.
My workbook is already protected at the sheet and workbook level. I am aware that when a workbook is protected, the ability to hide or unhide sheet tabs is removed. I should have used Display/Not Display sheet tabs as my post title.
So, if a user goes to File | Options | Advanced | Display options for this workbook, he/she can make all of the sheets show or not show regardless whether workbook protection is active or not, hence, removing from the user the ability to navigate through the workbook by clicking on whatever tabs are visible. I hope this makes sense.
My question was utilizing the angle that if a user uses the File | Options | Advanced | Display options for this workbook option to check the "Show sheet tabs" option and then clicks OK, if there is a way for the workbook to recognize that the activesheet is being asked to show/display the tabs and if so, through VBA to prevent it.
[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"DisplaySheetTabs.jpg","data-attachmentid":1197916}[/ATTACH]
The Show sheet tabs checkbox does not remove the tabs, it actually removes the tabs toolbar.
In vba code it's
ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.DisplayWorkbookTabs = True
If your intent is to prevent the user from showing the worksheets,
when your macro ends, hide your sheets. when macro starts, unhide them
sheets("sheetname").visible = false
sheets("sheetname").visible = true
Can you give me a little more detail as to what you wish to accomplish?
Thank you rabsofty and Infomage for your feedback I do appreciate it.
I'll have to apologize because I do not believe I am communicating what I am looking for well enough. Let's see if I can do better: So rabsofty, you are in the right path, the ActiveWindow.DisplayWorkbookTabs is the command I am utilizing which of course is the VBA equivalent of the pic I posted regarding the route of File | Options...and so forth. I am aware that the latter (VBA or otherwise) does not remove the tabs but merely removes them from the user's sight. Infomage, I am actually utilizing the very hidden method and it is not what I am desiring to do.
So back to the ActiveWindow.DisplayWorkbookTabs command - I am looking to use this very command BUT, I need to execute (via VBA) **IF** the user uses the File | Options | Advanced | Display options for this workbook to manually attempt to display the tabs again. In other words, let's say I'm the user and I want to "peek" into the tabs of the workbook and I have enough knowledge to make the sheet tabs appear/show via the File | Options | Advanced | Display options for this workbook check box <-- **THIS** is what I am attempting to prevent. Once the user checks the latter box and clicks OK, I need for the workbook to "look and feel" that the tabs are now displaying, provide a "naughty user" message box, and, re-hide them again.
I've attempted to use the following workbook event but, unfortunately, the code will only fire once the sheet becomes active - which I suppose will work, but, I'd rather have the code fire BEFORE than after. I hope I've explained it better and it makes better sense:
Don’t have an account yet? Register yourself now and be a part of our community!
