Results 1 to 6 of 6

Thread: Security of Pivot via VBA

  1. #1

    Question Security of Pivot via VBA



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    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:-)

  3. #3
    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

  4. #4
    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

  5. #5
    Mahat,

    Thank you again. I am going to have a play with this see what is possible.

    Tania

  6. #6
    Very interesting

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •