PDA

View Full Version : Userform - Different rights for differenz users



joshua1990
12-27-2017, 10:01 AM
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.


21248

21249


Do someone have an idea?


Best regards!

joshua1990
12-28-2017, 04:10 AM
Is there maybe already a existing concept for this approach?

SamT
12-28-2017, 07:31 AM
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
12-28-2017, 09:23 AM
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
12-31-2017, 07:15 PM
.
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
01-03-2018, 12:09 AM
Thanks for this fantastic concept!
I need some time to understand (:

Logit
01-03-2018, 09:16 AM
.
You are welcome.