Solved

Excel Data Protection

Posted on 2013-12-24
5
163 Views
Last Modified: 2013-12-25
Say I've an Excel spreadsheet with sensitive data in it that I wish to share in such a way that I can only let other users see the data filtered by my filter column.

If I put a 1 in the allow column then they must be able to see the data in it.
I'm aware of protecting the Sheets - can you think of another way?
I was attempting to create a data source to it and defining a query that only selects the data where there is a 1 in the 'allow' column - but as soon as I protect the source xls an error - encrypted data is returned - cannot read by the xls.
Any suggestions please?
0
Comment
Question by:shaunwingin
  • 3
  • 2
5 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 39738627
You might consider putting the data on a worksheet whose visible property is xlVeryHidden. You could then use a macro to perform the filtering and retrieve the filtered data to a visible worksheet.

To toggle the visibility of the worksheet:
Sub Visibility()
Worksheets("Confidential").Visible = IIf(Worksheets("Confidential").Visible = xlVeryHidden, True, xlVeryHidden)
End Sub

Open in new window

0
 
LVL 81

Expert Comment

by:byundt
ID: 39738643
Here is a macro that will filter data on a hidden worksheet, then paste it in Sheet1.
Sub CopyFilteredData()
Dim rgCopy As Range, rgDest As Range
Application.ScreenUpdating = False
With Worksheets("Confidential")
    Set rgCopy = .Range("A1").CurrentRegion     'The data table. Header labels in first row.
    rgCopy.Cells(1, 1).AutoFilter
    rgCopy.AutoFilter Field:=3, Criteria1:="1"  'Column C (Field:=3) equals 1 if data are to be visible
End With
With Worksheets("Sheet1")
    Set rgDest = .Range("A1").CurrentRegion
End With
rgDest.ClearContents
rgCopy.Copy
rgDest.Cells(1, 1).PasteSpecial xlPasteValues
Set rgDest = rgDest.Cells(1, 1).CurrentRegion
rgDest.Columns(3).ClearContents     'Clear the column with Visibility switches
End Sub

Open in new window

FilterDataOnHiddenSheetQ28325280.xlsm
0
 

Author Comment

by:shaunwingin
ID: 39738919
Tx. I didn't specify but I would really like one xls so that any comments or changes made are reflected in the original xls. This is easy with the built in Protect sheet functionality - but not so easy with my 2nd method or with the macro method.

Seems simplest to use protect sheet method ....
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39739297
In Quantum Entanglement, changes made to one object are immediately reflected in its mate even when the two are separated at a distance. The conditions have to be perfect for this to occur however, which is why you don't see it in everyday life.

While it is possible for two worksheets to be linked together so changes to one are immediately reflected in the other, the conditions have to be right for that to occur as well. First of all, you need to be able to map one worksheet to the other. Since you are filtering one sheet but not the other, that means you need a unique identifier for each row. Second, the layout needs to be known, because it is a VBA event-driven macro that will pulling the strings in the background. Third, some changes (such as formatting or cell comments) don't trigger event macros, so extra steps are required if you need to capture those.

I changed the previously posted sub to turn events off in contemplation of an event-driven mapping sub. I also allowed the database to have a variable number of columns (as long as the rightmost one has a header label of "Visible").
Sub CopyFilteredData()
Dim rgCopy As Range, rgDest As Range
Dim iCol As Variant
Application.ScreenUpdating = False
Application.EnableEvents = False
With Worksheets("Confidential")
    Set rgCopy = .Range("A1").CurrentRegion     'The data table. Header labels in first row.
    rgCopy.Cells(1, 1).AutoFilter
    iCol = Application.Match("Visible", rgCopy.Rows(1), 0)
    If IsError(iCol) Then
        MsgBox ("The visibility switches must be in the rightmost column. This column must have a header label ""Visible""")
        Exit Sub
    End If
    
    rgCopy.AutoFilter Field:=iCol, Criteria1:="1"  'Visible column equals 1 if data are to be visible
End With
With Worksheets("Sheet1")
    Set rgDest = .Range("A1").CurrentRegion
End With
rgDest.ClearContents
rgCopy.Copy
rgDest.Cells(1, 1).PasteSpecial xlPasteValues
Set rgDest = rgDest.Cells(1, 1).CurrentRegion
rgDest.Columns(iCol).ClearContents     'Clear the Visible column
Application.EnableEvents = True
End Sub

Open in new window

The event-driven mapping sub must go in the code pane of the worksheet with the copied data. As written, it assumes that column A is a unique ID. If it is duplicated in your original data, then the wrong row may be updated.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range, rgCopy As Range, rgOrig As Range, rw As Range
Dim v As Variant
Set rgCopy = Range("A1").CurrentRegion     'The copied data
Set rgCopy = Intersect(Target, rgCopy)
If rgCopy Is Nothing Then Exit Sub

Set rgOrig = Worksheets("Confidential").Range("A1").CurrentRegion       'The original data table. Header labels in first row.
Set rgCopy = rgCopy.EntireRow.Resize(, rgOrig.Columns.Count - 1)        'Don't copy over the Visibility column
For Each rw In rgCopy.Rows
    If rw.Cells(1, 1) <> "" Then
        Set cel = Nothing
        Set cel = rgOrig.Columns(1).Find(rw.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If Not cel Is Nothing Then
            rw.Copy cel
        End If
    End If
Next

End Sub

Open in new window

Note: The Worksheet_Change macro will only copy back changes made to the original database columns (less the last one for visibility). In other words, if user adds a comment to the right of the columns with header labels, they won't be reported back to Confidential worksheet.

I planned for user comments by adding a Comment column to the database in Confidential worksheet.
FilterDataOnHiddenSheetQ28325280.xlsm
0
 

Author Closing Comment

by:shaunwingin
ID: 39739368
Very professional - tx.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question