Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Just to confirm about encryption (XL 2003 and <)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Loungers

    Is there any MS-Excel encryption utilities, even freeware, that I can use to hide the contents of a worksheet, but still keep VBA able to work with the contents of the worksheet?

    Thanks a Million <img src=/S/money.gif border=0 alt=money width=17 height=15>

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Just to confirm about encryption (XL 2003 and <)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> Legare

    I did all what you proposed. I think that takes care of ~90% of the users.

    But then you have the remaining 10% who are the Loungers <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. You can't make MS-Excel 100% fool proof.

    As an experiment I went to the Immediate Window and I typed:

    ?activeworkbook.worksheets.count and I got 2 There are two worksheets. One xlVeryhidden the one with the data, and a dummy one.

    Then I typed

    ?worksheets(2).range("A1").value and I got Name.

    And that is what is in Sheet database, index 2, and in cell A1

    The worksheet is, as I said earlier, xlVeryHidden, hidden and also it is protected against all options, and linking to a cell in a worksheet in a workbook is AFAIK <font color=red>undefeatable</font color=red>. This is what you can do as a cheap resort, once you find out how many sheets are there, you can conceivably and very easily write code to pipe all that is in these sheets into a new workbook.

    There is no way around that <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> except by encrypting the contents so that if you do this linking/referencing think, at least you get garbage.

    Darn it!!!

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Just to confirm about encryption (XL 2003 and <)

    Hi Wassim,

    A couple of points to note:

    1. If you have any cells on the visible sheet referencing ranges in the hidden sheet, that will give away the hidden sheet's existence too - including the sheet's name.

    2. If you reference ranges on the hidden sheet via named ranges, those named ranges will likewise appear in formulae, and their addresses on the hidden sheet can be accessed via the auditing toolbar. Hiding them from the 'Insert|Name' dialogue via ThisWorkbook.Names("MyRange").Visible = False doesn't change that.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

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

    Re: Just to confirm about encryption (XL 2003 and <)

    About the best you can do is to make the font color for ther sheet the same as the background color, protect the sheet, then in VBA code make it VeryHidden:

    <pre> Worksheets("Sheet1").Visible = xlVeryHidden
    </pre>


    Nothing in Excel is secure from someone determined to get in. Someone would have to know the sheet name to make the sheet visible, and then know the password or crack it, then know to change the font color to see what is in the sheet.

    Edited by Legare Coleman to correct a spelling error in the VBA statement. Thanks for spotting it Hans.
    Legare Coleman

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Just to confirm about encryption (XL 2003 and <)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> macropod

    All I have are two sheets, one with some dummy data on it, stuff that would make you think you are looking at the real deal, and the other with the gold mine in it, is set to XlVeryHidden via VBA code.

    The worksheet was protected and the VBA project is also protected.

    With this, we get:
    1) You can't get to the VBA project.
    2) You can't "easily" see the worksheet.

    But the idea of opening a second workbook, and using it with some VBA to pipe out all the stuff on the hidden worksheet, via Workooks(2).Worksheets(2).Range("A1").value and so on for all cells. It will take a bit of time, but it is so doable when the intent is there.

    I am hoping that if I could add something to prevent the above, then Excel will be secure enough. I will work on some ideas, but I am sure there is a huge whole to plug and it won't be easy, even darn imposible.

    Maybe if we had something like "conditional formatting" in such a way that if the cell itself could be aware of what is happening to it.

    Explanation:

    If the cell itself can detect that there is a reference pointing to it, it will block such reference. So the cell can scan a workbook and if it sees its name, A1 in the example above, it will break it by changing the cell reference to a dummy empty cell. Come on give me a break, I just watched 6 hours of SiFi movies, and I know I need a <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22> but that would be so cool.

    Think of all the <img src=/S/dragon.gif border=0 alt=dragon width=17 height=15>viruses one can implement if this was true. <img src=/S/devil.gif border=0 alt=devil width=15 height=15>Nighmare City 24/7

    Thanks again

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Just to confirm about encryption (XL 2003 and <)

    I have seen some encryption/decryption VB code somewhere, e.g. here . You could use that to read/write to the cells in your (very)hidden sheet.
    But it would mean you would have to type in the encryption key everytime you open the workbook, because you cannot include it in the VBA code because it is very simple to crack.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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