Secure column data in Excel

[src2]

I need to secure a column of sensitive data in an excel spreadsheet where the only persons with the proper password or access level should be able to view the column.
I've looked at protecting the sheet, workbook, format-security and haven't found the right combination. Can anyone help with this?

Thanks a lot

 

[SkipVought]

Hi,

One way to HIDE data in Rows/Columns is to HIDE the Rows/Columns.

When the person with the proper stuff gives the proper password, you UNPROTECT the sheet and UNHIDE the Rows/Columns and then REHIDE the Rows/Columns & REPROTECT the sheet when they are done.



Skip,
[sub]
[red][/red]
[/sub]

 

[DirkStruan]

I will assume the other columns should be editable.

Highlight all cells, Format them (I usually rightclick for this option), on Protection deselect the Lock Cells.

Highlight the column you want to protect. Format cells. Lock cells. While it is highlighted, Format menu, Columns, Hide.

Tools, Protection, Protect Sheet. Include a password.

If you are trying to protect the data from change, this will work fine. If you are trying to protect the data from being read, you may want to look for something else as it's easy to enter a function referring to the hidden cells so you can read what is there.


Dirk

Everything useful perishes through use....Should I not rejoice at being useless?

 

[src2]

Thanks Dirk

Yes I would like to prevent the column from being read. Any ideas?

 

[SkipVought]

This is EXCEL, and not a secure application.

I don't believe there is any way of preventing a resourceful advanced user from reading ANYTHING in Excel.

You can make it DIFFICULT, but not impossible IMHO.

I hid a row.
I protected the sheet
I i made the sheet VERY HIDDEN

I can still "see" the value in the Very Hidden Sheet/hidden row/protected using VBA.

Skip,
[sub]
[red][/red]
[/sub]

 

[DirkStruan]

That's a bit more tricky if the other users know a bit about excel.

What is the type of info that you are trying to hide? How is it used? How much do the users need to be able to see? How many cells in the column are to be kept secret?

If the users need to enter a few parameters and then read a few output cells I would set up an input and output sheet and then have all parameters and calculations on a different sheet. If the results cells (on the calc sheet) are then named, and the output on the input/output sheet only refers to them by name the users will be none the wiser about the inputs.

If you take this approach, name the calc sheet something obscure/alphanumeric and then write a quick macro:

Sheets("obscure_name_here").visible = xlveryhidden

Then protect the VBA Project (rightclick and complete the protection tab), then you can protect worksheets/workbook as you see fit and, unless the other users guess the name of worksheet or the VBA Project password it should be reasonably safe. There are still ways round it though if the other users are a bit good.

I'm going to be heading off very shortly (it's gone 19:30 in the UK) so if any others out there want to jump into this thread, please feel free. I will check back in for your reply tomorrow.

D

 

[DirkStruan]

src2 - Skip is right & I totally agree with what he's said. My ideas are to simply slow down the less-knowledgeable.

xlveryhidden is usually enough to confound the majority of users I work with, then again so it turning the text color white on the cells with the parameters in!

 

[SkipVought]

Code like this does not need to know ANYTHING about sheet names, protection, hidden sheets.

It just displays the data that's there regardless...

Sub test()
    Dim ws As Worksheet, rg As Range, s As String
    For Each ws In Worksheets
        For Each rg In ws.UsedRange
            If Not IsEmpty(rg.Value) Then
                s = ws.Name & "!" & rg.Address
                If Left(rg.Formula, 1) = "=" Then
                    s = s & "(" & rg.Formula & ")"
                End If
                s = s & "(" & rg.Value & ")"
                MsgBox s
            End If
        Next
    Next
End Sub

Skip,
[sub]
[red][/red]
[/sub]

 

[dallen43]

I don't know exactly what you are doing, but would it be possible to have two different spreadsheets. The first without the sensitive data could be placed in a public folder while the other that does contain the sensitive data could be placed in a private folder.

Then there could be a macro that would update one spreadsheet from the other.

I admit, this is like using a sledge hammer on a thumb nail. But since Excel is not a secure application, you may have to use the security of the operating system.

Deb

 

[src2]

Thanks to everyone for their suggestions. I'm not sure if the person who wants to hide the data will want to go to all the trouble. I'll suggest the veryhidden but I bet they will choose to keep 2 spreadsheets and manually keep them synched.