Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel Data Protection

Posted on 2013-12-24
5
Medium Priority
?
170 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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,…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

609 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