1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel hide code

Discussion in 'Business Applications' started by scouse13, Apr 26, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. scouse13

    scouse13 Thread Starter

    Joined:
    Oct 29, 2009
    Messages:
    137
    I have a workbook with at the moment 3 worksheets in it,once i have finished them,i would like to put some code in so that when the workbook is opened the vertical and horizontal scroll bars, the sheet tabs and the main bar along the top of excel, file,edit,view, insert etc are not visable or available, any idea's on what code to use please, i know i can go into tools - options and untick the scroll bars and sheet tab sections so these do not show but i can't find anywhere to unshow the top section, i would prefer this to be done automatcally when the workbook opens.

    if possible could i then set up a macro on one of the worksheets(that i could password protect) that would restore these functions if i needed to alter any parts of the sheets at a later date

    i do apologise for the long winded question
    many thanks fo any help offered
     
  2. pcs365_4

    pcs365_4

    Joined:
    Apr 13, 2010
    Messages:
    77
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    The following code does part of what you want:
    First is toggles the setting, if visible then invisible and v.v.

    Sub ToggleHeaders()
    ActiveWindow.DisplayHeadings = Not ActiveWindow.DisplayHeadings
    Application.DisplayFormulaBar = Not Application.DisplayFormulaBar
    End Sub

    Turns Headers and Formula bar off
    Sub HeadersOff()
    ActiveWindow.DisplayHeadings = False
    Application.DisplayFormulaBar = False
    End Sub

    Turns Headers and Formula bar on
    Sub HeadersOn()
    ActiveWindow.DisplayHeadings = True
    Application.DisplayFormulaBar = True
    End Sub

    To hide the tabs, I think what you mean is to hide the sheets,
    There are two options, Hidden en Very Hidden
    If Didden you can unhide thme via the menu
    If Very Hidden then you can only unhide them via code

    I found a lot of explanations and how to's in the following site:
    http://www.cpearson.com/Excel/Topic.aspx

    You can copy and paste it use modify it, and I'm sure you'll find your answer there.
    If you found what you need and need some extra help yous holler.
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I put the code in the sample sheet with some buttons
     

    Attached Files:

  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi, I just added a password module that I found some long time ago and frequently use in my coding.
    It illustrates how you can allow certain functions (like unhide a Very Hidden sheet) :)

    There is a text box with a short explanation and the buttons to try.:D
     

    Attached Files:

  6. scouse13

    scouse13 Thread Starter

    Joined:
    Oct 29, 2009
    Messages:
    137
    thanks keeballah altered bits of the code to suit my needs on hiding etc,and this works a treat when the workbook is opened, but couldn't quite follow the password bit. let me try to explain
    on the menu page i have set up a form button that calls a user form for a password to be entered, the userform has one textbox and one command button,what i would like to happen is when a password is enterd into the textbox and then the command button is pressed, if the password entered is correct then a sheet called "settings" is opened,if the password is incorrect then a msgbox appears informing the user,"password incorrect" and the userform close's.
    any help appreciated, also where do i set up my password?
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    If it's just a password verification, why a textbox, just invoke the function and if true it's okay to continue else it's not ok. See the sample I sent you.
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/919413

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice