3

Looking for some help on VBA User restrictions. So far I have the code pasted below. It is working perfectly, but I want to build on it.

I have it so the specific users listed have access to the file, and anyone else who tries to access the file gets a msgbox saying they aren't authorized and then the book closes.

I am however hoping that some of the users can see some sheets (the sheets they shouldn't see will be xlveryhidden) And then the other users can see the other sheets listed...

ie: Name 1 can see sheet 13, Name2 can see sheet14 and sheet3 Name 3 can see sheet22 sheet23 and sheet4 In terms of security it isn't hugely important, they are all from the same team, but just for user friendly and tidy document.

Private Sub Workbook_Open()
 Dim Users As Variant
 Dim UName As String
 Dim UFind As Variant
 Users = Array("Name1", "Name2", "Name3", "Name4", "Name5")

 UName = Environ("UserName")
 On Error Resume Next
 UFind = WorksheetFunction.Match(UName, Users, 0)
 If Err <> 0 Then
     MsgBox "You are not authorised to use this Workbook"
     ThisWorkbook.Close SaveChanges:=False
 End If
End Sub
Improve this question

2 Answers 2

Reset to default
1

Came up with an answer, it is pretty simple, and wont withstand new users being added, but for the mean time it is ok...

Private Sub Workbook_Open()
 Dim Users As Variant
 Dim UName As String
 Dim UFind As Variant
 Users = Array("Name1", "Name2", "Name3")

 UName = Environ("UserName")
 On Error Resume Next
 UFind = WorksheetFunction.Match(UName, Users, 0)
 If UName = "Name2" Then
 Worksheets("Sheet23").Visible = True
 Worksheets("SHEET17").Visible = True

 ElseIf UName = "Name1" Then
 Worksheets("Sheet23").Visible = True
 Worksheets("SHEET17").Visible = True
 Worksheets("Sheet4").Visible = True


 ElseIf UName = "Name3" Then
 Worksheets("Sheet23").Visible = True
 Worksheets("SHEET17").Visible = True

 ElseIf Err <> 0 Then
     MsgBox "You are not authorised to use this Workbook"
     ThisWorkbook.Close SaveChanges:=False
 End If
 End Sub

And in order to re-hide them all again when closing the file:

SubPrivate Sub Workbook_BeforeClose(Cancel As Boolean)

Worksheets("Sheet23").Visible = False
Worksheets("SHEET17").Visible = False
Worksheets("Sheet4").Visible = False
Worksheets("Sheet1").Visible = False

‘If you don’t save it’s not effective
Me.Save End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Nicely done! Just two remarks: (1) Take a look at Select Case ... End Select, it has a better structure than lots of ElseIfs. (2) You can alternatively use Workbook_BeforeSave. This has the advantage, that the User can decide not to save...
0

Make changes in your If condition as:

If Err <> 0 Then
    MsgBox "You are not authorised to use this Workbook"
    ThisWorkbook.Close SaveChanges:=False
Else
    For Each ws In Worksheets
        If ws.Name <> "Sheet" & UFind Then
            ws.Visible = xlSheetHidden
        End If
    Next ws
End If

Make sure that the sheet names are Sheet1, Sheet2, Sheet3, .. etc as mentioned in the question.

Improve this answer

3 Comments

Thanks for the response - I'm a little confused though, how would this work? Is this not just unhiding sheets to everyone? I'm looking for something that specifies Name1 can see Sheet3 and Sheet 4, Name2 can see sheet4 and Sheet5. (As an example)
@user5836742 - Oops! I think I didn't understand your question completely. My code will make Sheet1 visible to Name1, Sheet2 visible to Name2 and so on...
Ah ok, do you have any recommendations for what I am looking for?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.