Great way to Hide Sensitive Data

[yankee428]

As we all know, Excel is not a secure environment by any means. If you want application security you need to step up to Access at a minimum , if not mySQL. So if you need to share a workbook with others, but don't want to give them rights to see the whole workbook, how do you hide certain data, yet still have access to it yourself.

There are tons of password hacks out there, so forget Excel Protection. And Hiding sheets is too easy to get around. You need to hide and lock down the hide.

1. create a workbook with two worksheets
2. create secret data table on sheet(2)
3. rename sheet(2) to a difficult to guess name (I'll use difficult!)
4. insert a combo box built from control toolbox on sheet(1) and give it the code from below(change parameters where needed)
5. in VBA, make difficult! xlVeryHidden
6. now password protect the VBA project

Users of the file now have read and lookup access to part of your data while you still hide the other part. The same could be done for data input using a textbox. Just make sure that you do not reference the name of the secret worksheet anywhere that is visible to the user.

Disclaimer: There is one work around that could be used to compromise this method, but as far as I can see, this is as good as it gets.

Private Sub ComboBox1_Change()
Dim lkupval As String
Dim tblarray As Variant
Dim rslt As String
On Error GoTo errorhandler
'!!!!!!!!!!!!!!!!!!!!!!SET THE FOLLOWING FOUR PARAMATERS ACCORDING TO YOUR SHEET!!!!!!!!!!!!!!!!!!!!
lkupval = Sheets(1).Range("AA1")
tblarray = Sheets(2).Range("A1:D4")
lkupval2 = "AA1"
rslt = "F4"
If lkupval = "" Then
    Sheets(1).Range(rslt) = ""
    ComboBox1.Value = ""
    Exit Sub
End If
Cells(4, 6) = Application.WorksheetFunction.VLookup(lkupval, tblarray, 3, False)
Exit Sub
errorhandler:
    MsgBox "Please Choose from the drop down list"
    ComboBox1.Value = ""
    Sheets(1).Range(lkupval2).Value = ""
    Sheets(1).Range("F4").Select
End Sub

 

[mForteed]

hash?

can someone get a little more plain english about what a hash is ... why can't the file save the actual password itself? it must generate this hash? where can I learn more about this stuff?

 

[Nimrod]

Hello mForteed

In short a "hash" is basically the encrypted password. From this posting what you really need to know is this..... excel is not secure any 12 year can hack it in about 5 minutes.... however CIO's will take alot longer to hack it . :wink:

 

[Jon Peltier]

however it's a pretty good way to prevent very basic , honest , un-curious users from seeing source data ".... :wink: .... and has always had a place in my quiver of sol'ns

Whenever I provide the first version of anything to a client, before I trust that they'll pay up for the work, I put in a simple function that won't work past a certain date, and I lock down the VBA. I tell them that I've done this, and they just assume that it's unbreakable. So I guess it's a good psychological barrier.