CODEWITHSUNDEEP
vbaexcel
user5836742
user5836742

Reputation: 43

VBA hide sheets from specific users

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

Upvotes: 3

Views: 3154

Answers (2)

user5836742
user5836742

Reputation: 43

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

Upvotes: 1

Mrig
Mrig

Reputation: 11712

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.

Upvotes: 0

Related Questions