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

    Disable Tools/Options (Excel 2002/2003/2007)

    HI

    I would like anyone who uses a 'password protected' workbook to be unable
    to use the Tools -> Options in the menu bar to allow them to tick the boxes
    to show gridlines, tabs, rows & columns, formulas, formula bar etc. Can't
    find anything that will allow me to protect the workbook in this manner -
    are you able to help or is this simple not possible.

    Regards

    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 29 Times in 29 Posts

    Re: Disable Tools/Options (Excel 2002/2003/2007)

    Why would you want to do that? It would irritate me tremendously if someone did that to me.

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

    Re: Disable Tools/Options (Excel 2002/2003/2007)

    Hi Hans

    I totally agree, as this is a request from a third party, I will ask the question.

    Regards

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

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

    Re: Disable Tools/Options (Excel 2002/2003/2007)

    HI Hans

    Please see the reply

    Mainly for aesthetics - workbook contains around 137 sheets which will be
    given to all our F&B team as a read only reference library and I would like
    it to look more professionally produced as well as no one being able to mess
    about with the settings.

    Regards

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

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

    Re: Disable Tools/Options (Excel 2002/2003/2007)

    Personally, I wouldn't bother with it, but the following code in the ThisWorkbook module will disable Tools | Options... entirely as long as the workbook is active. It will be enabled when the user switches to another workbook.

    Private Sub Workbook_Activate()
    Application.CommandBars("Worksheet Menu Bar"). _
    FindControl(ID:=522, Recursive:=True).Enabled = False
    End Sub

    Private Sub Workbook_Deactivate()
    Application.CommandBars("Worksheet Menu Bar"). _
    FindControl(ID:=522, Recursive:=True).Enabled = True
    End Sub

    522 is the ID of Tools | Options...

    Note: I have custom buttons on my toolbar to toggle gridlines and row/column headers on and off. Such custom buttons will still work.

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

    Re: Disable Tools/Options (Excel 2002/2003/2007)

    Hi Hans

    I am in total agreement with your sentiments. It's down to the Conscience of the workbook owner

    Many thanks for the code.

    Regards

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

  7. #7
    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: Disable Tools/Options (Excel 2002/2003/2007)

    For the Excel tools I develop I also 'clean up' the sheet by removing grid lines and row/col markers so I understand why your client wants this. However, given that I also agree with the other posters who say it's not a good idea (since harder to evaluate formulas), I'd say it depends on why they want it done.

    In my case I have code that switches between what I call user mode and admin mode. User mode (the default) protects/hides sheets that are not for the user to view, hides grid lines, etc. Since the workbook is for data entry only, not to edit or view formulas (that's my job, the designer) they don't need to see it. If they want to to see what's under the hood (to confirm I'm not doing any voodoo-math), they can enter a password from a button I provide and view everything (except the VBA code). I only give the password out to certain people. (Yes, Excel is trivial to break into, but it's for the casual user and if I'm hiding super secret stuff, I won't put it in Excel anyway.)

    <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>
    Deb

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

    Re: Disable Tools/Options (Excel 2002/2003/2007)

    Hi Deb

    Thank you for your most enlightening comments on this matter.

    Regards

    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
  •