Hide specific sheet based on logged in user

[sn0365]

Hello,

I am creating a incident form using a user form, which will feed in data in sheet1 and sheet2. When the user submits the user form, all the data from the user form will be send to sheet 1 and only specific data will be sent to sheet 2.

I would like to permanently hide sheet 1 from all user except few. Example - I would like the vba script that would look at the logged in user who is trying to run the excel file. if the logged in user matches the one that have been allowed to view sheet 1, sheet 1 automatically become available to that user. For any other user sheet 1 is permanently hidden with the option that the user cannot unhide it manually from the FORMAT button.

Thank you very much for your help.

regards,
Sumit

 

[mrshl9898]

You could perhaps use something like this, in "This Workbook" (not sure what the format button is)

Private Sub Workbook_Open()


Sheets("Sheet1").Visible = xlVeryHidden


If Environ$("Username") = "Andrew.Marshall" Then
Sheets("Sheet1").Visible = True
End If


End Sub

 

[Logit]

.
Here is a basic approach :

Sub ShowSheets()
Dim pWord As String
     'Prompt the user for a password and unhide the worksheet if correct
    Select Case InputBox("Please enter the password to unhide the sheet", _
        "abc")
         
    Case pWord
        MsgBox "Sorry, that password is incorrect!", _
        vbCritical + vbOKOnly, "You are not authorized!"
        'ActiveWorkbook.Close
    Case Else
        With Worksheets("CURRENT BILLING")
            .Visible = xlSheetVisible
            .Activate
            .Range("A1").Select
        End With
        With Worksheets("BILLING SUMMARY")
            .Visible = xlSheetVisible
            .Activate
            .Range("A1").Select
        End With
    End Select
End Sub


Sub hideSheetsAgn()
    Worksheets("CURRENT BILLING").Visible = xlSheetVeryHidden
    Worksheets("BILLING SUMMARY").Visible = xlSheetVeryHidden
End Sub

Download : https://www.amazon.com/clouddrive/share/ML55WfeHDjAONLbSrkq8JxnoZ5nggFkT3ZQg4wg3A0B