Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

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

                          If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

                          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

                              Trending

                              Collapse

                              • cayusbonus
                                From week numbers to standard date format
                                cayusbonus
                                Hi all, I am quite new in VBA, but after two weeks I have learnt a lot thanks to this forum, so I feel kind of in debt with it. I have been recently looking for an example where VBA converts from week dates to the regular mm/dd/yyyy date format, but I couldn't find a satisfactory answer. Yes, we have the weeknum function to be used in a spreadsheet but so far I couldn't call it in my VBA code. So therefore, I generated the code below that applies the ISO 8601 standard to make such a conversion. Hope it helps to somebody.


                                Code:
                                Sub WeeksToDates()
                                'Converting YYWW.DD to MM/DD/YYYY dates according ISO 8601. More info https://en.wikipedia.org/wiki/ISO_week_date
                                Dim Todayf As String        'Date in yywk.dd format
                                Dim Yearf As Integer        'Year of the input date
                                ...
                                1 day ago
                              • Reafidy
                                Textbox Class Object
                                Reafidy
                                Hi All,

                                I recently had a request for a better method of controlling multiple textboxes on a userform. "I have lots of textboxes on a userform I want the user to only be able to enter numbers in all of them!"

                                The solution: Making use of a class module. The method below will alow you to handle the event for all textboxs at once without having to add events for each individual textbox on the form.

                                Add a class module and name it "clsObjHandler"

                                Code:
                                Option Explicit
                                Private WithEvents tbxCustom1 As MSForms.TextBox    'Custom Textbox
                                Public Property Set Control(tbxNew As MSForms.TextBox)
                                    Set tbxCustom1 = tbxNew
                                End Property
                                Private Sub tbxCustom1_Change()
                                'Message Box To Display
                                ...
                                December 11th, 2007, 05:32
                              • Furybringerx
                                Parsing HTML Table to Excel
                                Furybringerx
                                My personal project this past week has been trying to parse a HTML table to Excel.
                                I've used many resources to finally get the code I'm looking for. It has taken a week to figure it out so I feel that I should share it to help others like me.

                                Code:
                                Sub ParseTable()
                                Dim IE As InternetExplorer
                                Dim htmldoc As MSHTML.IHTMLDocument 'Document object
                                Dim eleColtr As MSHTML.IHTMLElementCollection 'Element collection for tr tags
                                Dim eleColtd As MSHTML.IHTMLElementCollection 'Element collection for td tags
                                Dim eleRow As MSHTML.IHTMLElement 'Row elements
                                Dim eleCol As MSHTML.IHTMLElement 'Column elements
                                Dim ieURL As String 'URL
                                
                                'Open InternetExplorer
                                Set IE = CreateObject("InternetExplorer.Application")
                                IE.Visible
                                ...
                                December 11th, 2013, 05:39
                              Working...
                              X