Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Here's a fun one...

    I recently discovered that one of my users has found the way to unhide sheets. This is where my masterlink page is and by deleting a row in that page, they threw off the entire matrix.

    I want to create a procedure that brings up a custom error box, admonishing any user when going to Format/sheet/unhide.


    After one incident of "busted!", I just may want to gray out the Format/sheet/unhide capability, but for now, I just want to have some fun.

    I already know the UserForm part.
    Any ideas on how to do this?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Here's a fun one...

    You can intercept the Unhide command, but that requires replacing the command on the menu with your own, and that gets very complicated. The easy thing to do would be to use the Worksheet_Activate event for the sheet to display your message.
    Legare Coleman

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Here's a fun one...

    If the workbook is password protected, the user can't unhide sheets.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Here's a fun one...

    Yeah, but I wanted to 'bust them'!!
    A nice message box would've been alot of fun.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Here's a fun one...

    Since I have no competence in VBA, I would be interested in seing any code you have which "slaps the hands" of anyone trying to mess with unavailable menus.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Here's a fun one...

    From Excel help:
    "Note If sheets are hidden by a Visual Basic program that assigns the property xlVeryHidden, you cannot use the Unhide command to display the sheets."

    If you use:
    Worksheets("SheetNameHere").Visible = xlVeryHidden
    in VBA code, your users will need to know VBA to unhide the sheets.

    FWIW

  7. #7
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Here's a fun one...

    Where does this code go?
    How is it activated?

    As an addendum...How does one inhibit the ability to cut i.e.(ctl+x) data out of cells?
    Everytime they do this, it ruins the link down the line.
    I'm assuming that this is really easy and something I just missed on the way.

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Here's a fun one...

    I'm a VBA novice and would like to use this code in AutoOpen on a distributed spreadsheet.

    Private Sub Workbook_Open()
    Worksheets("MySheetNameHere").Visible = xlVeryHidden
    ...

    But I have to ask a really dumb question! If "MySheetNameHere" is already hidden and the workbook is password protected, does this code have any value, and, do I need to vary the above line of code if the Sheet is already hidden (since the sheet is not ".Visible")?
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Here's a fun one...

    Yes, that code would still have value. If the workbook is password protected, that only affects opening the workbook. Once they have opened it by providing the password, then they would still be able to unhide hidden sheets (just hidden, not xlVeryHidden) if you haven't done this code. If the sheet is already xlVeryHidden, the code will not change anything, but it will not do any harm.
    Legare Coleman

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Here's a fun one...

    Hi John,
    If the workbook is password protected you'll probably get an error every time you open it. It doesn't matter whether the worksheet is already 'veryhidden', it will simply reset the visible property to 'veryhidden' (which is why you'll get an error if the workbook is protected - Excel views the attempt to reset the property as a change, albeit a change that makes no difference)
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Here's a fun one...

    The code has, IMHO, little value due to the existence of the macro enable /disable box that your users will get when they open the spreadsheet. If macro virus protection were turned off (no, you can't turn it off in workbook_open, I think it must be triggered by something like before_workbook_open) then it possibly would have the value of allowing you to unhide the sheet when you're working on the file, but not having the hassle of having to remember to hide the sheet before closing. Not a lot of added value IMHO but worth considering.

    The code will work fine whatever the value of .Visible - or should do. This is like painting a red postbox red again. (Okay, it's nothing like it, but I hope the example works!)

    As for the password protection, I think this may be a sidetrack - unless you've locked the workbook in the VBE.

    FWIW, I would keep the 'hiding' code in a personal tools file and lock the project in the VBE. That way the user can't disable any code and can't get at the sheet through the VBE - .Visible can be set in the properties panel there as well as via code. Then all you have to worry about is whether the user has his own personal tools file with an 'unVeryHidden' proceedure. Or am I starting to get paranoid?

    HTH

    Brooke

  12. #12
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Here's a fun one...

    Sorry to butt in here, but what about some Mickey Mouse logic.

    Can you hide everything and let the Auto_Open macro unhide what you want them to see? This way, if they dis-able macros, they don't get to see anything anyway.

  13. #13
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Here's a fun one...

    Now that's good. I take it there's no royalty payments?

    Brooke

  14. #14
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Here's a fun one...

    Absolutely not! [img]/w3timages/icons/smile.gif[/img]

    And of course, you could just leave one sheet visible which has your "Oh, no you don't" message on it.

  15. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Here's a fun one...

    Legare, Rory, and Brooke, thank you for your guidance.

    A couple of clarifications; this is a distributd pricing model. Users never get the passwords (each sheet and the workbook have individual passwords), and the VBA modules on this workbook are also password protected, and all Private. Users don't need the passwords; everything they need including results is in a visible protected sheet called "Input"; all the calcs and source data are on a hidden sheet.

    Now I have a bigger crisis; I have turned the hidden sheet into an xlVeryHidden and can't unhide it!

    Private Sub Workbook_Open()
    Worksheets("MyWorksheetnameHere").Visible = True

    doesn't seem to work. Help!
    -John ... I float in liquid gardens
    UTC -7ąDS

Page 1 of 3 123 LastLast

Posting Permissions

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