Announcement

Collapse
No announcement yet.

Userid And Password To Access Worksheets

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    ExcelFox

  • #2
    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?
    Reafidy

    Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

    Comment


    • #3
      Re: Userid And Password To Access Worksheets

      Excellent even better, thanks for sharing krish.
      Reafidy

      Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

      Comment


      • #4
        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

        Comment


        • #5
          Re: Userid And Password To Access Worksheets

          Hi,


          Hit Alt + F11 to see the code.
          Kris

          ExcelFox

          Comment


          • #6
            Re: Userid And Password To Access Worksheets

            Excellent code. Very informative

            Many thanks Kris

            Comment


            • #7
              Re: Userid And Password To Access Worksheets

              Excellent Solution thanks a lot Krish

              Comment


              • #8
                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

                Comment


                • #9
                  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]
                  Kris

                  ExcelFox

                  Comment


                  • #10
                    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

                    Comment


                    • #11
                      Re: Userid And Password To Access Worksheets

                      Hi,

                      It only shows 'Login' sheet in the beginning.
                      Kris

                      ExcelFox

                      Comment


                      • #12
                        Re: Userid And Password To Access Worksheets

                        Hi Krish, this is so cool. How can I set read only permission in the same format? Please advise

                        Comment


                        • #13
                          Re: Userid And Password To Access Worksheets

                          Take a look at this example

                          PassWordForm7
                          Hope that Helps

                          Roy

                          New users should read the Forum Rules before posting

                          For free Excel tools & articles visit my web site

                          RoyUK's Web Site

                          royUK's Database Form

                          Where to paste code from the Forum

                          About me.

                          Comment


                          • #14
                            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.

                            Comment


                            • #15


                              Re: Userid And Password To Access Worksheets

                              kaelo2014, this forum is called "TIP, TRICKS & CODE (NO QUESTIONS)"

                              If you have a question regarding the content, please update your existing question or start a new thread and provide a link to this thread.

                              Regards,

                              S O

                              Comment

                              Working...
                              X