Unhide a Sheet when User Matchs into a list

[TEIXEIRE]

Hello,

Sorry, I am introducing in VBA codes,

I have a sheet called "AuthUsers", and in collunm A, I have the windows name (not the user but the name registered) of the responsibles in each row,

I want to open the workbook, and unhiden the sheet called "Week Update", if the Applicatin.UserName matchs in any row of the sheet "AuthUsers"

I did the following code, but didnt work.
Could u help me on that ?

Sub Authorized_Report_Users()
'Only for hide/unhide specific sheets for users who is responsible to keep report updated

    Var1 = WorksheetFunction.Match(Application.UserName, Sheets("AuthUsers").Columns(1), 0)

    If Not WorksheetFunction.IsError(Var1) Then

        Sheets("Week Update").Visible = True

    End If


End Sub

 

[steve the fish]

Try using Application.Match

 

[Logit]

.
Paste these macros in the ThisWorkbook module :

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Sheets("WEEK UPDATES").Visible = True Then
        Sheets("WEEK UPDATES").Visible = False
    End If
End Sub

Private Sub Workbook_Open()
   ChkUsers
End Sub

Paste this macro in a Regular Module :

Option Explicit

Sub ChkUsers()
Dim x As String
Application.ScreenUpdating = False
Sheets("AUTH USERS").Visible = True
    With Sheets("AUTH USERS")
        x = .Range("A2:A10").Find(Environ("Username")) '<-- change the range to suit
      
        If x = Environ("Username") Then
            Sheets("WEEK UPDATES").Visible = True
        End If
    End With
Sheets("AUTH USERS").Visible = False
Application.ScreenUpdating = True
End Sub

Keep in mind the UserName is CASE SENSITIVE. Therefore, if the Username is displayed as : UserName .... you will need to enter it in the AUTH USERS sheet as UserName.
If you enter it on the AUTH USERS sheet as Username ... or userName .... or username .... the macro will not work.

 

[TEIXEIRE]

Thank you so much, it seems will work, but for now, the system return this message.

 

[Logit]

I don't understand why it is generating an error. It functions without error here.

Try this ... insert the line On Error Resume Next where indicated in the example below :

Option Explicit

Sub ChkUsers()
Dim x As String

On Error Resume Next

Application.ScreenUpdating = False
Sheets("AUTH USERS").Visible = True
    With Sheets("AUTH USERS")

 

[Fluff]

You will get that error if nothing is found.
Are you trying to find the windows login user name, or the excel user name?

 

[Logit]

Thanks Fluff !

TEIXEIRE

Make certain you add you own Username to the list as well. If you haven't done that, that is the reason you are receiving the error while testing on your machine.

 

[TEIXEIRE]

Ok, bur for other member which shouldnt have access/visible the sheet "work update", will find an error everytime the open the file ?

Honestly, I prefere the name of the person instead of the user access. e.g. Eloize Teixeire instead of teixeire, so having that in mind would be Applcation.UserName, instead of Environ("Username"), right ?

 

[TEIXEIRE]

I changed to Application.UserName, and with name worked well
also because of the onerror resume next and the ELSE

)

 

[Logit]

Great. Can you share your final macro code ?