Userform - Different rights for differenz users

[joshua1990]

Hey guys!

I'm currently working on a big data table with sensitive data.
The objective now is to create an useform that starts with the workbookopen_event.
The user should then have the choice between different users to choose and enter the appropriate password. The difficult thing is, it should generically contain a table that is only visible to the admin, which contains all users and all tables. The following is shown.



The user should then have the choice between different users to choose and enter the appropriate password. The difficult thing is, it should generically contain a table that is only visible to the admin, all users and all tables. The following is shown.


Unbenannt.jpg

Mappe1.xlsx


Do someone have an idea?


Best regards!

[joshua1990]

Is there maybe already a existing concept for this approach?

[SamT]

There are many ways to do what you want. They are all different and they all depend on how your Workbook is structured. Since we don't know that, we can't have any meaningful ideas for you.

We need to see more than just the "Permissions" table, we need to see all the sheets and all the tables. They don't have to contain any data, but we need to see the physical Structure.

With the information given so far, all we can say is

Write some code to read the "Permissions" table and show the sheets as indicated therein.

[joshua1990]

There classic data sheets.
Something beetwen 5 and 15 columuns and round about 3000 rows.
No big deals, somt of them have a data list so the user can choose options (2-5 different).

Do you need for this classic data sheet(s) an example?
Of course, I will deliver the example file, if you really need!


Thanks for the advice and help!
Best regards.

[Logit]

.
Paste in USERFORM :

Dim HFD As Integer, HFR As Integer
Dim N As Long, F As Long, Pass As String
Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Sheets("SetUp").Visible = xlSheetVisible
        For N = 3 To HFR
            If ComboBox1.Value = Sheets("SetUp").Cells(15, N).Value Then
                Exit For
            End If
        Next N
        
    If TextBox1.Value = Sheets("SetUp").Cells(16, N).Value Then
        Sheets("SetUp").Visible = xlSheetVeryHidden
        MsgBox Range("SetUp!C10").Value, , Range("SetUp!C9").Value & " " & Sheets("SetUp").Cells(15, N).Value
        Unload UserForm1
        Sheets("SetUp").Visible = xlSheetVisible
        Pass = Sheets("SetUp").Range("K12").Value
        Sheets("SetUp").Visible = xlSheetVeryHidden
        
        For F = 17 To HFD
            If UCase(Sheets("SetUp").Cells(F, N).Value) = "X" Then
        
            Sheets(Sheets("SetUp").Cells(F, 2).Value).Visible = xlSheetVisible
            End If
            
            If UCase(Sheets("SetUp").Cells(F, N).Value) = "P" Then
                Sheets(Sheets("SetUp").Cells(F, 2).Value).Visible = xlSheetVisible
                Sheets(Sheets("SetUp").Cells(F, 2).Value).Protect Password:=Pass
            End If
        Next F
    Else
        MsgBox Range("SetUp!C6").Value, , Range("SetUp!C7").Value
        TextBox1.Value = ""
        Sheets("SetUp").Visible = xlSheetVeryHidden
    End If
    
    Application.ScreenUpdating = True
End Sub
Private Sub CommandButton2_Click()
    Unload UserForm1
End Sub
Private Sub UserForm_Initialize()


Dim WkSht As Worksheet
Application.ScreenUpdating = False
    For Each WkSht In Worksheets
        If Not WkSht.Name = "Intro" Then WkSht.Visible = xlSheetVeryHidden
    Next WkSht
    
    Sheets("SetUp").Visible = xlSheetVisible
    HFD = Sheets("SetUp").Range("B65536").End(xlUp).Row
    HFR = Sheets("SetUp").Range("IV15").End(xlToLeft).Column
    UserForm1.Caption = Range("SetUp!C3").Value
    Label3.Caption = Range("SetUp!C4").Value
    
    For N = 3 To HFR
        With ComboBox1
            .AddItem Sheets("SetUp").Cells(15, N).Value
        End With
    Next N
Sheets("SetUp").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub

Paste in THISWORKBOOK :

Option Explicit
Dim WSht As Worksheet, Pass As String


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call HideAll
End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Call HideAll
    ThisWorkbook.Saved = True
End Sub


Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Sheets("SetUp").Visible = xlSheetVisible
    Pass = Sheets("SetUp").Range("K12").Value
    Sheets("SetUp").Visible = xlSheetVeryHidden
        For Each WSht In ActiveWorkbook.Worksheets
            Sheets(WSht.Name).Unprotect Password:=Pass
        Next WSht
    Call HideAll
    UserForm1.Show
End Sub
Sub HideAll()
    Application.ScreenUpdating = False
    On Error Resume Next
        For Each WSht In ActiveWorkbook.Worksheets
            Sheets(WSht.Name).Unprotect Password:=Sheets(Sheets("SetUp").Cells(30, 11).Value)
            If WSht.Name <> "Intro" Then WSht.Visible = xlSheetVeryHidden
        Next WSht
    Application.ScreenUpdating = True
End Sub

[joshua1990]

Thanks for this fantastic concept!
I need some time to understand (:

[Logit]

.
You are welcome.