Solved

Excel Data Protection

Posted on 2013-12-24
5
161 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 80

Expert Comment

by:byundt
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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
Comment Utility
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 80

Accepted Solution

by:
byundt earned 500 total points
Comment Utility
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
Comment Utility
Very professional - tx.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
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 simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now