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.

Excel VBA: Hide and lock specific ROWS from viewing without Password

Discussion in 'Business Applications' started by MRdNk, May 12, 2009.

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

    MRdNk Thread Starter

    Joined:
    Apr 7, 2007
    Messages:
    439
    Hi All,

    I'm pretty good at Excel and VBA, however I'm a little stumped on a request I've had from one of my Excel users. What we want to be able to achieve, if even possible, is for particular rows to be hidden & locked unless the user has a specific password.

    The table is laid out proper table format, with each row representing a record, and each column representing a field.

    We want to be able to hide and lock from viewing the top, say, 5 records from view, as we need to be able to distribute the whole workbook via Email and other Web services, whilst keeping the top 5 sensitive records hidden, until a password is typed in.

    Any ideas, any one.

    Ps. I realise a database would make more sense, unfortunately this is unpractical, as we use WAN servers (not good for Access), and several external contractors, who need a copy of this workbook. As well as Senior Management, not wanting an Access DB.
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Hi (... I never know what to call you, in my head; ) Mr D(?) :D

    Ideas? Sure. PM me & I'll send you code to trash whatever password you use to lock the top, say, 5 records in a matter of seconds.

    Do you get what I'm saying? :)
     
  3. MRdNk

    MRdNk Thread Starter

    Joined:
    Apr 7, 2007
    Messages:
    439
    Doesn't need to be ultra-secure, just as secure as the standard Excel security via a password.

    As long as we make some effort, if others purposely break it or hack it, we can deal with that as a separate issue (ie. sue them for serious breach of contract).
     
  4. MRdNk

    MRdNk Thread Starter

    Joined:
    Apr 7, 2007
    Messages:
    439
    Ps. It's Mister DNK (as initials).
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Let me put it another way.

    Say rows 1 thru 5 are hidden, and the sheet is protected with a password.

    So you send me your wb and I don't know the sheet password.

    I just press F5 -- type 1:5 -- click OK -- press CTRL+C -- paste into a brand new wb -- unhide the rows.
     
  6. MRdNk

    MRdNk Thread Starter

    Joined:
    Apr 7, 2007
    Messages:
    439
    So that's not the solution.

    So would it be better to have the raw data on another sheet, and only fill row 1:3 with data from the hidden worksheet fill-in the open viewable worksheet if the user types in the correct password to a cell?

    The best and most secure way is to have sensitive records on a completely different workbook and only send this to people with permission to view it.
    However, I would like to be able to offer some sort of alternative solution if one is possible with basic security - password.
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Obviously "sensitive records on a completely different workbook" is guaranteed secure.

    Hiding rows 1:3 is completely obvious -- "wonder why the top row isn't numbered 1?"

    Hiding another sheet -- how long till someone spots Format > Sheet ... "wonder why 'Unhide' isn't greyed out?"

    However, if you set the Visible property (in the VBE) of "another sheet" to VeryHidden before you send out the book ... ;)

    Can "Senior Management" handle:

    (i) insert 3 rows at the top

    (ii) enter =Hidden!A1 in A1

    (iii) drag it across & down

    ?

    At the risk of repeating myself, "sensitive records on a completely different workbook" is guaranteed secure.

    :)
     
  8. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    If the file to be sent is for information only, then I’d suggest a PDF of the file after you’ve hidden the desired rows.
     
  9. 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/826504

  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