Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Thread: Userid And Password To Access Worksheets

  1. #1
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Userid And Password To Access Worksheets

    Hi All,

    You'll see lot of examples, but here is a different one.

    sample user name: admin
    password: pass

    Let me know if there is any bugs.

    Enjoy!!
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

  2. #2
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    4,271

    Re: Userid And Password To Access Worksheets

    Hey Krish,

    Thats very cool thanks.

    EDIT: Dont know if its a bug or not but I notice there is no way to close without saving?

  3. #3
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    4,271

    Re: Userid And Password To Access Worksheets

    Excellent even better, thanks for sharing krish.

  4. #4
    Join Date
    25th January 2011
    Posts
    1

    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Userid And Password To Access Worksheets

    Hi,


    Hit Alt + F11 to see the code.

  6. #6
    Join Date
    18th January 2011
    Posts
    1

    Re: Userid And Password To Access Worksheets

    Excellent code. Very informative

    Many thanks Kris

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    21st April 2011
    Posts
    4

    Re: Userid And Password To Access Worksheets

    Excellent Solution thanks a lot Krish

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    24th February 2011
    Posts
    11

    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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    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 
    
    
    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 
    
    

  10. #10
    Join Date
    24th February 2011
    Posts
    11

    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Password Protect Multiple Worksheets
    By Agnes in forum Excel General
    Replies: 2
    Last Post: March 3rd, 2008, 16:24
  2. Password Protect Worksheets From Viewing
    By jagzed in forum Excel General
    Replies: 11
    Last Post: January 4th, 2007, 22:44
  3. Replies: 3
    Last Post: May 19th, 2005, 07:56
  4. Password protecting worksheets
    By Chester in forum Excel General
    Replies: 3
    Last Post: March 29th, 2003, 01:58
  5. Protect worksheets without any password
    By XL-Dennis in forum TIP, TRICKS & CODE
    Replies: 0
    Last Post: February 19th, 2003, 09:01

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno