Security of Pivot via VBA

**tania** · 2013-12-27, 11:55 AM

Hi Everyone,

A great forum and some really good help in times of need. Thank you to you all.
I have a question and am not sure if this is possible. I have a pivot table which i want to be able to secure based on the a filter. So for example

I have filters as follows

Language

English
Dutch
Spanish
Italian
Greek
French

These filters are selected by each member of our team and then can view associated figures to the language. What i want to do is lock the data down so only users who need English Data can view English data, only Spanish users can access Spanish data etc.
Is there any way which could work where when a filter is selected it prompts for a password for that Language and a master password which unlocks all data.
Not sure if I am having a blonde moment so do excuse the ignorance, but is it possible even to set password of the filter at a Windows NT level, for example. I know the list of users who need access to English only data and want to restrict them only to English data in the Pivot based on NT credentials, is this something which is possible.
Any guidance on this would be a great help, thank you to you all.

Tania

**mahat** · 2013-12-31, 08:45 PM

Hi Tania,
usually access privileges should be managed within the database the PivotTable connects to. Use the domain\userid then no additional password needs to be entered (single sign on) and you don't need to mess around with VBA...
The advantage is that future PivotTables based on this database will then automatically also behave the way you want:-)

**tania** · 2014-01-02, 09:21 AM

Hi Mahat,

Thank you for the reply. There is no external source for the data. Everything is contained in the same spreadsheet. And I need to modify it for each set of users to be restricted to their language types.
Is there anyway to restrict the filter view like i stated above via VBA or any other method. Even if it means setting several passwords, for example

User A requires English only therefore

If User A selects English from the Filter, code is executed and brings up password prompt that only enables the English Filter
User B comes along and requires Spanish again the same happens.

Just trying to find anyway this is possible at all?

Thanks

Tania

**mahat** · 2014-01-02, 11:13 AM

Hi Tania,
Work with the OS username und use the PivotTableUpdate event. If you succeed I recommend you not only hide the data source sheet but set it to xlSheetVeryHidden - Else what's the use restricting the PivotTable when users still can access all the source data! You may start with something like:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim strUser As String
Dim strGermanUser As String
Dim strEnglishUser As String
Dim strLanguage As String

strGermanUser = "User1, User2, User3,..."
strEnglishUser = "User20, User21, User22,..."

strUser = LCase(Environ("USERNAME"))

If InStr(1, strGermanUser, strUser, vbTextCompare) Then
strLanguage = "German"
Else
strLanguage = "English"
End If

Select Case strLanguage
Case "German"
'Check if the pivot table fields comply to what German users are allowed to see
'If not display MsgBox and roll back the changes
'In order to roll back to the previous selected filter settings it is necessary
'to write them into a hidden sheet for example.
Case "English"
'Check if the pivot table fields comply to what English users are allowed to see
'If not display MsgBox and roll back the changes
'In order to roll back to the previous selected filter settings it is necessary
'to write them into a hidden sheet for example.
End Select
End Sub