Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,948
    Thanks
    2
    Thanked 1 Time in 1 Post

    Hidden Tabs (Excel 2002)

    Hi

    I have hidden the tabs in a multiuser spreadsheet, is there any way I can stop them being unhidden?

    Many thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Hidden Tabs (Excel 2002)

    If you go into the Visual Basic Editor, you can set the Visible property of a worksheet to xlSheetVeryHidden. This means that the sheet will not be listed if the user selects Format | Sheet | Unhide... The sheet can only be unhidden using Visual Basic (or in the Visual Basic Editor).

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,948
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Hidden Tabs (Excel 2002)

    HI Hans

    Thanks for your reply, much appreciated.

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hidden Tabs (Excel 2002)

    Just FYI..... Hiding the tabs and hiding the sheets are not the same thing. I see HansV gave you the info on how to hide the sheets so they can't be viewed by the user, but did you really man that you wanted to not show the tabs at all? You can do this manually from the Tools/Options menu (View tab). Unless the workbook is protected, the user can still go the next/previous sheet by using Ctrl+PageUp/Down to navigate the workbook.

    I often turn off the tabs in my custom Excel apps because I have a left-hand navigation bar on all vislble sheets that I use instead. The user clicks the navigation buttons like a web page to go from sheet to sheet instead of hitting the sheet tabs directly. In m ycase, I need to make these apps act more like standalone applications, not Excel files so I create my own user interface. It's probably much more than most people need to do though.

    Thnx,
    Deb

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,948
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Hidden Tabs (Excel 2002)

    Hi Deb

    Thanks for your input, but I have prevented Page up and Page down with the following code.

    Others might find this useful.

    These two macros should be placed in the ThisWorkbook object. The
    first is run whenever the workbook is activated and it disables
    Ctrl+Page Up and Ctrl+Page Down by having nothing run when they are
    pressed. The second macro is run when the workbook is deactivated, and
    re-enables the keys.


    Regards
    Braddy

    Private Sub Workbook_Activate()
    Application.OnKey "^{PgDn}", ""
    Application.OnKey "^{PgUp}", ""
    End Sub

    Private Sub Workbook_Deactivate()
    Application.OnKey "^{PgDn}"
    Application.OnKey "^{PgUp}"
    End Sub
    If you are a fool at forty, you will always be a fool

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hidden Tabs (Excel 2002)

    The code isn't needed if the sheets to be avoid are VeryHidden. In my case I don't care if they jump from sheet to sheet with Ctrl+PgUp/Down I just want to make sure that if they do that (instead of using my custom navigation buttons) that they don't view sheets I don't want them to see. So for my uses, VeryHIdden is the only solution. I've used your code to capture the F1 key when I have my own custom help feature and then disable it when a different workbook is active so that F1 runs Excel's help instead.

    Thnx,
    Deb

  7. #7
    Lounger
    Join Date
    Jan 2005
    Location
    Gwent, Wales
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hidden Tabs (Excel 2002)

    I have just tried to use your code to disable the F1 key, but I can't get it to work?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Hidden Tabs (Excel 2002)

    The code you need is
    <code>
    Private Sub Workbook_Activate()
    Application.OnKey "{F1}", ""
    End Sub

    Private Sub Workbook_Deactivate()
    Application.OnKey "{F1}"
    End Sub
    </code>
    You MUST put this code in the ThisWorkbook module of the workbook in which you want to disable F1. It won't work if you put it in a standard module. See attached workbook.

  9. #9
    Lounger
    Join Date
    Jan 2005
    Location
    Gwent, Wales
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hidden Tabs (Excel 2002)

    Thanks, that works great.

  10. #10
    Star Lounger
    Join Date
    Mar 2003
    Location
    Sydney, New South Wales, Australia
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hidden Tabs (Excel 2002)

    Very hidden is great for the average user as long as he doesn't have a utility such as Navigator Utilities which routinely shows all sheets, including very hidden sheets, and allows you to easily make sheets very hidden and easily unhide them without writing code.

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,948
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Hidden Tabs (Excel 2002)

    Hi Mark

    It is adequate for my needs, but thanks for the advice.


    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •