Regarding Hiding/Unhiding Sheet tabs - Excel 2010

  • 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

  • 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?

  • Code
    sheets("sheetname").visible = xlveryhidden

    will make the sheet hidden, and hide it from the unhide context menu... Will that do?


    In order to display it again, VBA must run to unhide it.

  • 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:


    Code
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    
    
       If ActiveWindow.DisplayWorkbookTabs = True Then
         ActiveWindow.DisplayWorkbookTabs = False
       End If
    
    
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!