locked
Require Password to Launch UserForm RRS feed

  • Question

  • Hello!

    I've created a searchable database in Excel with user friendly search interface using UserForms. Some of the data in the database (which is hidden from user view and information only retrievable via the search form) includes information I don't want all users to have access to.  I have created a separate search form from the general access user interface, that will return search results from the database with this sensitive information included, however, I want to lock down access to this search form by requiring a password which only authorised persons will be provided.

    Basically, upon clicking a command button, I want the user to enter a password which, when correctly entered, launches the restricted search form.

    Is there a way to do this using Visual Basic? I'm using Office 365 ProPlus.

    Thanks!

    Tuesday, July 14, 2020 6:36 AM

Answers

  • To:  IiahM
    re:  password required

    Something like this...
    '---
    Sub Test()
     Dim response As String
     response = Application.InputBox("Enter Password", "Restricted Access", "**********", , , , , 2)
     If response = "antsinyourpants" Then
      ' RestrictedSearchForm.Show False
     ElseIf response = "False" Then
       Exit Sub
     Else
       VBA.MsgBox "Comrade Boris has been notified.   ", vbExclamation, "INCORRECT PASSWORD"
     End If
    End Sub
    '---

    The drawback, to the above, is the entry into the InputBox is visible to any shoulder surfers.
    A Textbox displayed on a UserForm has a property to display an asterisk for each character entered.

    '---


    Free Excel programs at MediaFire (no ads)...
    The "Insert_Rows" add-in is now available.
    http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents


    • Edited by Nothing Left to Lose Tuesday, July 14, 2020 7:05 PM Added Else If
    • Marked as answer by KiahM Thursday, July 16, 2020 12:40 AM
    Tuesday, July 14, 2020 6:58 PM
  • To:  KiahM
    re:  code problem

    You omitted the InputBox line.
    Also, Sub... at the start of the code and End Sub at the end - one of each
    This slightly revised version should do what you want...
    '---
    Private Sub cmdLogin_Click()
     Dim response As Variant
     response = Application.InputBox("Enter Password", "Restricted Access", "**********", , , , , 2)
     If response = "antsinyourpants" Then
       'Correct password entered, so do something here. <<<< Note
     ElseIf response = False Then
       Exit Sub
     Else
       VBA.MsgBox "The Password you entered is incorrect." & vbCr & _
       "Please contact the Document Owner if the error persists.   ", _
       vbExclamation, "INCORRECT PASSWORD"
     End If
    End Sub
    '---


    http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

    • Marked as answer by KiahM Thursday, July 16, 2020 1:57 AM
    Thursday, July 16, 2020 1:39 AM

All replies

  • This forum is for VB.NET not coding in Excel which I would assume when you mention a user form so I'm moving your question to the Excel for developer forum.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, July 14, 2020 10:22 AM
  • To:  IiahM
    re:  password required

    Something like this...
    '---
    Sub Test()
     Dim response As String
     response = Application.InputBox("Enter Password", "Restricted Access", "**********", , , , , 2)
     If response = "antsinyourpants" Then
      ' RestrictedSearchForm.Show False
     ElseIf response = "False" Then
       Exit Sub
     Else
       VBA.MsgBox "Comrade Boris has been notified.   ", vbExclamation, "INCORRECT PASSWORD"
     End If
    End Sub
    '---

    The drawback, to the above, is the entry into the InputBox is visible to any shoulder surfers.
    A Textbox displayed on a UserForm has a property to display an asterisk for each character entered.

    '---


    Free Excel programs at MediaFire (no ads)...
    The "Insert_Rows" add-in is now available.
    http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents


    • Edited by Nothing Left to Lose Tuesday, July 14, 2020 7:05 PM Added Else If
    • Marked as answer by KiahM Thursday, July 16, 2020 12:40 AM
    Tuesday, July 14, 2020 6:58 PM
  • Thanks for the tip! This is working well, however I am having one issue:

    I've included the following code in the login userform, but receive an 'Expected End Sub' error when clicking the login button which refers to the first line of code:

    Private Sub cmdLogin_Click()
    Sub Test()
     Dim response As String
     If response = "antsinyourpants" Then
      'StaffProfile_UI.Show False
     ElseIf response = "False" Then
       Exit Sub
     Else
       VBA.MsgBox "The Password you entered is incorrect. Please contact the Document Owner if error persists.   ", vbExclamation, "INCORRECT PASSWORD"
     End If
     End Sub

    If I remove the Sub Test() line, the Password Incorrect msg box appears even when the password is entered correctly.  If I enter an End Sub following the Private Sub line, the code doesn't run when the command button is clicked.

    Sorry for the beginner questions, but any idea what change I need to make to get this to work?

    Thursday, July 16, 2020 12:59 AM
  • To:  KiahM
    re:  code problem

    You omitted the InputBox line.
    Also, Sub... at the start of the code and End Sub at the end - one of each
    This slightly revised version should do what you want...
    '---
    Private Sub cmdLogin_Click()
     Dim response As Variant
     response = Application.InputBox("Enter Password", "Restricted Access", "**********", , , , , 2)
     If response = "antsinyourpants" Then
       'Correct password entered, so do something here. <<<< Note
     ElseIf response = False Then
       Exit Sub
     Else
       VBA.MsgBox "The Password you entered is incorrect." & vbCr & _
       "Please contact the Document Owner if the error persists.   ", _
       vbExclamation, "INCORRECT PASSWORD"
     End If
    End Sub
    '---


    http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

    • Marked as answer by KiahM Thursday, July 16, 2020 1:57 AM
    Thursday, July 16, 2020 1:39 AM
  • Ah, that's fixed it! Thank you!
    Thursday, July 16, 2020 1:57 AM