VB Password Issue

G

gmazza76

Well-known Member
Joined
Mar 19, 2011
Messages
771
Office Version
  1. 365
Platform
  1. Windows
Afternoon,

I have been having issues with loading a password into a userform. And i am wondering where below i am asking the VB to match a value in combobox1 if it can be more than 1 option.

With this i mean i have 30 managers who have 3 people working issues they have. Is there anyway i can write the following

If UserForm2.ComboBox1 = "Manager1", "Manager2", "Manager 3" etc..
If it doesnt match the first set it looks at the next set of names. I think i may need to create a loop for the 3 sets but i am unsure of how to do this.

All help appreciated
Cheers:)

Code: 
Dim gotcha As Boolean
Dim iRow As Long
Dim ws As Worksheet
Dim lRow As Integer
Dim Pword
Dim cell As Range
Dim I As Long
Set myBook = Workbooks.Open(Filename:="C:\Users\MAZZA\Documents\Gavin\Car Park\FYvData.xls")     'Home
  
    Set ws = myBook.Worksheets("Sheet1")
    If UserForm2.ComboBox1 = "Laura Haynes" Then
        Pword = InputBox("Please enter password")
        If Pword = "ticket" Then
            ActiveWorkbook.Sheets("Sheet1").Activate
        Else
            MsgBox "Please contact your administrator for a password"
            myBook.Close True
            Exit Sub
        End If
    End If
    Unload Me
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Re: VB Passwrod Issue

Try like this

Code: 
If UserForm2.ComboBox1 = "Laura Haynes" Or UserForm2.ComboBox1 = "Benny Hill" Or UserForm2.ComboBox1 = "Fred Scuttle" Then

If more users than this consider a Select Case statement.
 
Upvote 0
Re: VB Passwrod Issue

I have tried the info suggested and it slows down.
I have realised the problem is that i am over complicating things when i only need to use a managers name. This should be a bit easier
 
Upvote 0
Re: VB Passwrod Issue

Thanks for the info.
Just 1 question if possible
Is there anyway i can get the information below to add another passwowd in for another set of managers etc.. like i think it would work below.

Is this at all possible???

Code: 
Set ws = myBook.Worksheets("Sheet1")
    If UserForm2.ComboBox1 = "Laura Haynes" Or UserForm2.ComboBox1 = "Benny Hill" Or UserForm2.ComboBox1 = "Fred Scuttle" Then
        Pword = InputBox("Please enter password")
        If Pword = "ticket" Then
            ActiveWorkbook.Sheets("Sheet1").Activate
   Else
      If Userform2.ComboBox1 = "Manager 2" or Userform2.ComboBox1 = "Manager 3" Then
         Pword = InputBox(Please enter password")
          If Pword ="pass" Then
           ActiveWorkbook.Sheets("Sheet1").Active
   Else
      If Userform2.ComboBox1 = "Manager 4" or Userform2.ComboBox1 = "Manager 5" Then
         Pword = InputBox(Please enter password")
          If Pword ="play" Then
           ActiveWorkbook.Sheets("Sheet1").Active
    Else
            MsgBox "Please contact your administrator for a password"
            myBook.Close True
            Exit Sub
      End If
  End If
End If
End If  
End If
End If
 
Last edited:
Upvote 0
Re: VB Passwrod Issue

This should simplify that code using a Select Case, which VoG suggested.
Code: 
Dim ws As Workbook
Dim strPWord As String
Dim pword
    Set ws = myBook.Worksheets("Sheet1")
    Select Case Userform2.ComboBox1.Value
        Case "Laura Haynes", "Benny Hill", "Fred Scuttle"
            strPWord = "ticket"
        Case "Manager 2", "Manager 3"
            strPWord = "pass"
        Case "Manager 4", "Manager 5"
            strPWord = "Play"
    End Select
    pword = InputBox("Please enter password")
    If pword = strPWord Then
        Application.Goto ActiveWorkbook.Sheets("Sheet1").Range("A1"), True
    Else
        MsgBox "Please contact your administrator for a password"
        myBook.Close True
        Exit Sub
    End If
You could make things a lot simpler by having a list of managers and there passwords on a worksheet somewhere.

A simple setup like this would do.

<TABLE style="WIDTH: 127pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=169><COLGROUP><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; WIDTH: 66pt; FONT-FAMILY: Calibri; BACKGROUND: black; HEIGHT: 15pt; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" height=20 width=88>Manager</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 61pt; FONT-FAMILY: Calibri; BACKGROUND: black; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" width=81>Password</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>Laura Haynes</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">ticket</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>Benny Hill</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">ticket</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>Fred Scuttle</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">ticket</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>Manager 2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">pass</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>Manager 3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">pass</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>Manager 4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">play</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>Manager 5</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: black 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: black 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">play</TD></TR></TBODY></TABLE>

Then you can use the first column to populate the combobox with the managers names.

When it comes to them entering a password you can extract check what's entered against what the password for that manager should be according to the list.

This list would be easy to maintain - you could add as many names as you wanted, change any passwords, delete managers etc...
 
Upvote 0
Many Thanks Norie.

I like the idea of the manager / password idea.
Do you have this in working form so i can have a look how it is written, or can you supply the amendment to the VB above.
I would hide the information on a sheet called "info" between cells H1:I10

I appreciate this as it saves a huge amount of programming that i am a beginner in.

Thanks
Gavin
 
Upvote 0
Gavin

I'll see what I can do - I was kind of working on it before but my connection dropped and I forgot to save anything before restart.

Excel might have a recovered/autosave copy kicking about.:)
 
Upvote 0
Cheers

I have just changed the VB to suit the spreadsheet etc.. as below but i keep getting a debug saying

"Run time error'424': Object required"

Is this due to missing Dim an object you have added as i am not sure what i should add

Cheers

Rich (BB code): 
Private Sub cmdsubmit_Click()
    
Workbooks.Open Filename:="C:\Users\MAZZA\Documents\Gavin\Car Park\FYvData.xls" 'Home
'Workbooks.Open Filename:="\\W2K6082\COMMON\SHARED\Gavin Mazza\Car Park\FYVData.xls"
 
On Error Resume Next
Set myBook = Application.Workbooks("FYVData.xls")
On Error GoTo 0
    
Dim myBook As Workbook
Dim gotcha As Boolean
Dim iRow As Long
Dim ws As Worksheet
Dim lRow As Integer
Dim strPword As String
Dim cell As Range
Dim I As Long

Set myBook = Workbooks.Open(Filename:="C:\Users\MAZZA\Documents\Gavin\Car Park\FYvData.xls")
    
    Set ws = myBook.Worksheets("Sheet1")
    Select Case UserForm2.ComboBox2.Value
        Case "Abdulaziz Abbas", "Christopher Allan", "Atiff Mazhar", "Brian Remers", _
             "Lee Smith", "John (TR) Sutcliffe", "Liz Warrender", "Tim Wilson"
            strPword = "ticket"
        Case "Adrian Donoghue", "Carole Todd", "Fiona Turner", "Helen Holland", "Keith Peabody", _
             "Paul Hylands", "Samantha Bevan", "Sue Longden"
            strPword = "darkside"
        Case "Bernadette Peabody", "Caroline Smethurst", "Hayley Kelsall", "Joanne Qadir", _
             "Kirstie Charnock", "Mark Franssens", "Paul Clark", "Stan Sandland", "Stuart Todd", _
             "Wendy Dumville"
            strPword = "sharples"
    End Select
    Pword = InputBox("Please enter password")
    If Pword = strPword Then
        Application.Goto ActiveWorkbook.Sheets("Sheet1").Range("A3"), True
    Else
        MsgBox "Please contact your administrator for a password"
        myBook.Close True
        Exit Sub
    End If
    Unload Me
    lRow = Range("C" & Rows.Count).End(xlUp).Row
    For Each cell In Range("C1:C" & lRow)
        If cell.Value = ComboBox2.Value Then
            For I = 3 To lRow
                If Cells(I, 1).Value = "" Then Exit For
                If Cells(I, 1).Value <> "" And Cells(I, 14).Value = "" Then
                    gotcha = True
                    UserForm1.Show False
                    UserForm1.txtdate.Value = Cells(I, 1)
                    UserForm1.TextBox1.Value = Cells(I, 3)
                    UserForm1.TextBox13.Value = Cells(I, 2)
                    UserForm1.TextBox2.Value = Cells(I, 4)
                    UserForm1.TextBox3.Value = Cells(I, 7)
                    UserForm1.TextBox6.Value = Cells(I, 5)
                    UserForm1.TextBox5.Value = Cells(I, 6)
                    UserForm1.TextBox4.Value = Cells(I, 8)
                    UserForm1.TextBox7.Value = Cells(I, 9)
                    UserForm1.TextBox8.Value = Cells(I, 10)
                    UserForm1.TextBox9.Value = Cells(I, 13)
                    UserForm1.TextBox14.Value = Cells(I, 11)
                    UserForm1.TextBox15.Value = Cells(I, 12)
                    UserForm1.TextBox11.Value = Cells(I, 4)
                End If
            Next I
            
            UserForm1.txtdate.Enabled = False
        End If
        If gotcha = False Then
            MsgBox "Nothing to Update"
            ActiveWindow.Close (True)
        End If
    
    Next cell
 
    gotcha = False
    
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Using Cell value in VBA as Password to Open file
Replies
3
Views
110
Kaper
Kaper
crook_101
  • Solved
VB to loop
Replies
5
Views
272
myall_blues
myall_blues
Z
  • Question Question
VBA Process Flow Chart using Shapes & Connectors
Replies
2
Views
269
zlotowlosapl
Z
C
  • Question Question
Force user to enter at least one symbol and one number in a textbox through a userform
Replies
9
Views
310
christianbiker
C
J
  • Question Question
Userform inconsistent responses
Replies
1
Views
218
Candyman8019
C

Forum statistics

Threads
1,224,600
Messages
6,179,836
Members
452,947
Latest member
Gerry_F

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top