Userid And Password To Access Worksheets

    Re: Userid And Password To Access Worksheets


    how can i obtain the VB code?
    i have the acces to the sheet but i dont know how use it in my books

    thanks

    Re: Userid And Password To Access Worksheets


    Helo Krish,
    It is wonderful to provide sheet access to the users. But, when i tried to protect the Workbook Structure with a password and saved the workbook, the error pops up" Run time error'1004'. Unable to set the visible property of the worksheet class. Have you any idea to solve this. Because users may delete the sheets if workbook is not protected in my case.


    Thanks.


    Lok

    Re: Userid And Password To Access Worksheets


    Hi,


    Replace the following procedures with this


    Thisworkbook module


    [vb]Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim i As Integer

    ThisWorkbook.Unprotect "pwd"
    Sheets("Macros Disabled").Visible = xlSheetVisible
    For i = 1 To Worksheets.Count
    If Sheets(i).Name <> "Macros Disabled" Then
    On Error Resume Next
    Sheets(i).Visible = xlVeryHidden
    End If
    Next
    On Error GoTo 0
    ThisWorkbook.Save


    End Sub
    Private Sub Workbook_Open()

    ThisWorkbook.Unprotect "pwd"
    With Sheets("Login")
    .Visible = xlSheetVisible
    .ScrollArea = "a1:l26"
    End With
    Sheets("Macros Disabled").Visible = xlSheetVeryHidden
    Sheets("DashBoard").Visible = xlSheetVeryHidden
    ThisWorkbook.Protect "pwd"
    UserForm1.Show


    End Sub[/vb]


    Userform1 module


    [vb]Private Sub cmdOK_Click()

    Dim a, u, p, w(), i As Long, db As Worksheet, Flg As Boolean
    Dim j As Long, x, y, c As Long, rSource As String
    Set db = Sheets("DashBoard"): Flg = False: c = 0: x = 0

    ThisWorkbook.Unprotect "pwd"

    With db
    a = .Range("a1").CurrentRegion
    End With
    u = UCase(Me.tbUN): p = Me.tbPW: Flg = False
    With Application
    x = .Match(u, .Index(a, 0, 1), 0)
    End With
    If Not IsError(x) Then
    If Application.Index(a, x, 2) = p Then Flg = True
    If Flg Then
    ReDim w(1 To UBound(a, 2) - 2)
    For j = 3 To UBound(a, 2)
    If UCase(a(x, j)) = "A" Then c = c + 1: w(c) = a(1, j)
    Next
    Else
    MsgBox "Incorrect Password", vbCritical + vbOKOnly, MyTitle
    Exit Sub
    End If
    Else
    MsgBox "Incorrect User Name", vbCritical + vbOKOnly, MyTitle
    Exit Sub
    End If
    For i = 1 To Sheets.Count
    If Sheets(i).Name <> "Login" Then
    If IsError(Application.Match(Sheets(i).Name, w, 0)) Then
    On Error Resume Next
    Sheets(i).Visible = xlVeryHidden
    Else
    Sheets(i).Visible = xlSheetVisible
    End If
    End If
    Next
    Sheets("Login").Visible = xlSheetVisible
    Sheets("Macros Disabled").Visible = xlSheetVeryHidden
    Sheets("DashBoard").Visible = xlSheetVeryHidden
    On Error GoTo 0
    With db.Range("aa1")
    .Resize(100).Clear
    .Value = "Sheet Names"
    .Offset(1).Resize(c) = Application.Transpose(w)
    End With
    ThisWorkbook.Protect "pwd"
    Unload Me
    UserForm2.Show


    End Sub[/vb]

    Re: Userid And Password To Access Worksheets


    Hi Krish,
    Thanks a lot for the hardwork. It works great. Is there a possibility that "user login form" is shown directly at the start of excel and donot show ANY Sheets. The sheets will be only shown after the correct login.


    Thank you for the help

    Re: Userid And Password To Access Worksheets


    hi Krish.
    i openned the file but how do i add additional user ids and password.
    so far only admin works.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login