VB Password Issue

[gmazza76]

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

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

 

[Norie]

Gavin

I thought this was a password issue?

I've created a workbook that has a userform that is just basically a login form using passwords and names from the worksheet.

I can uploaded and post a link but you seem to be doing a lot more than just checking passwords with the code you just posted.

Anyway here's the file http://www.box.net/shared/cnb3kspovi.

Back to the code - you seem to be opening a workbook mybook.

Then you create a reference to a worksheet in that workbook, ws.

You check the password next but after that you don't refer to the ws again.

The rest of the code does seem to be doing something with ranges/cells on some worksheet but it's not clear what worksheet it is.

I think it's the worksheet in the workbook you opened but I'm not sure.

 

[gmazza76]

Good Evening,

I have used the code you supplied as below but i am having 2 issues of you have a minute

1. I get a VB error as follows on the part that isa highlighted below 'compile error Expected: End of statement' & Syntax error
2. If i choose say "Hayley Kelsall" and use password lorraine it says it is incorrect and flashes the message up "Please contact your administrator for a password"

Any ideas

Private Sub cmdsubmit_Click()
    
Dim gotcha As Boolean
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"
Dim myBook As Workbook
Dim iRow As Long
Dim ws As Worksheet
Dim lRow As Integer
Dim strPWord As String
Dim pword
On Error Resume Next
Set myBook = Application.Workbooks("FYVData.xls")
On Error GoTo 0

    Set ws = Worksheets("Sheet1")
    Select Case UserForm2.ComboBox2.Value
        Case "Abdul Abbas", "Atiff Mazhar", "Brian Remers", "Christopher Allan", "Ingrid Carter", "John Sutcliffe", "Lee Smith", "Liz Warrender", "Tim Wilson"
            strPWord = ticket
        Case "Bernadette Peabody", "Caroline Smethurst", "Hayley Kelsall", "Joanne Qadir", "Kirstie Charnock", "Mark Franssens", "Paul Clark", "Stan Sandland", "Stuart Todd", "Wendy Dumville"
            strPWord = "lorraine"
        Case "Adrian Donoghue", "Carole Todd", "Fiona Turner", "Helen Holland", "Keith Peabody", "Paul Hylands", Samantha Bevan", "Sue Longden"
            'strPWord = "louise"
        End Select
        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
        
    Unload Me

 

[Norie]

As far as I can see the highlighted error is caused by a missing double quote, which is probably a typo.

As for the other problem - that could be caused by the name being entered incorrectly.

How are you populating the combobox with the manager names?

 

[gmazza76]

Afternoon,

They are all entered in the "Private Sub UserForm_Initialize()" as

userform2.combobox2
.AddItem "Abdul Abbas"
                .AddItem "Adrian Donoghue"
                .AddItem "Atiff Mazhar"
                .AddItem "Bernadette Peabody"
                .AddItem "Brian Remers"
                .AddItem "Carole Todd"
                .AddItem "Caroline Smethurst"
                .AddItem "Christopher Allan"
                .AddItem "Fiona Turner"
                .AddItem "Hayley Kelsall"
                .AddItem "Helen Holland"
                .AddItem "Ingrid Carter"
                .AddItem "Joanne Qadir"
                .AddItem "John Sutcliffe"
                .AddItem "Keith Peabody"
                .AddItem "Kirstie Charnock"
                .AddItem "Lee Smith"
                .AddItem "Liz Warrender"
                .AddItem "Mark Franssens"
                .AddItem "Paul Clark"
                .AddItem "Paul Hylands"
                .AddItem "Samantha Bevan"
                .AddItem "Stan Sandland"
                .AddItem "Stuart Todd"
                .AddItem "Sue Longden"
                .AddItem "Tim Wilson"
                .AddItem "Wendy Dumville"
            End With

The file you sent for managers names / passwords can i use this to launch the first part of the VB i have to load the rest of a userform??

Cheers

 

[Norie]

Gavin

So all the names are hard-coded? That could make it possible for typos/spelling errors to creep in.

As for the question about the file I uploaded, I'm not sure what you mean.

The file has a simple userform for the user to select the manager name from a combobox and enter the password.

The combobox is populated from columns H on worksheet called Info with each name having the relevant password in the same row of column I.

The user selects a name, enters the password and clicks the OK button.

If the password is correct the form is unloaded and focus placed on another worksheet.

If the password is incorrect the file is closed.

That's all it currently does, but it would be easy to add to what happens if the correct password is entered

I just don't know what it is you want to do if it is.

 

[gmazza76]

Afternoon,

I have used your code to create a new load userform which works great.In your code as far as i understand, if the correct password is entered it allows you to view the "Data" tab.

Is there anyway instead of it allowing me to go to the "Data" tab i can get it to run some VB to then check another spreadsheet and load another userform. The code below would work as it primarily checks another spreadsheet ( this is where i had the original problems with Passwords ). Matches the manager name to column "C" in the other sheet and then populates the userform2. when it has done this it then closes the other file down.

If need be i could send you a copy of both files for you to have a look @ if need be.

Many Thanks

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"
 
lRow = Range("C" & Rows.Count).End(xlUp).Row
    For Each cell In Range("C1:C" & lRow)
        If cell.Value = txtPass.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 = False
                    UserForm1.Show False
                    UserForm1.txtdate.Value = Cells(I, 1)
                    UserForm1.txtdate.Locked = True
                    UserForm1.TextBox1.Value = Cells(I, 3)
                    UserForm1.CmbCSM.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.TextBox16.Value = Cells(I, 4)
                End If
            Next I
            
            UserForm1.txtdate.Enabled = False
        End If
        If gotcha = True Then
            MsgBox "Nothing to Update"
            
        End If
    Next cell
    Workbooks("FYVData.xls").Close True
        
        ' code to close workbook
        ' commented out for testing
                
        'ThisWorkbook.Close False
    End If
    
End Sub

 

[Norie]

What do you want to populate the 2nd userform with exactly?

Which worksheet in which workbook is it populated from?

Is the code you've got already, which looks like it might be meant to do what you want, not working?

Also what userform do you actually want to populate? UserForm1 or UserForm2?

 

[gmazza76]

What do you want to populate the 2nd userform with exactly?
An agent at work pre populates a ( frmqualityform ) form that then saves all the data to the file below.
Myself or another supervisor loads Userform1 via ( via the password box you sent ) a button in the main worbook
This when loaded then brings up there enquiry, we add a couple of cells etc...
When submit is pressed on UserForm1 it then saves the information back to another sheet by managers manager
and then hopefully i will then be able to get it to delete the line of data out of "Sheet1".

The information is held in another file. The path below is where all the info is stored
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"

Which worksheet in which workbook is it populated from?
File - Workbooks.Open Filename:="C:\Users\MAZZA\Documents\Gavin\Car Park\FYvData.xls"
Sheet - "Sheet1"

Is the code you've got already, which looks like it might be meant to do what you want, not working?
It was working, but due to chopping and changing the way the info is found hasnt helped.
It currently says nothing to update but then pulls in the correct information depending on which manager is selected.
Also what userform do you actually want to populate? UserForm1 or UserForm2?
I need it to populate UserForm1.
The cell references are correct and do pull in all the correct information

I have most of the code working it is just adjusting things after i have added the password VB that you supplied as i
am unsure how to add this to your VB.

I hope this helps.
Many Thanks
Gavin

 

[Norie]

Gavin

I forgot to ask an important question.

What criteria are you using to get the data?

I assume it's something here, but I honestly can't follow what's going on.

lRow = Range("C" & Rows.Count).End(xlUp).Row
    For Each cell In Range("C1:C" & lRow)
        If cell.Value = txtPass.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

For example, why are you looping? Twice?

I actually thought you were trying to populate with multiple values - that would be the 'normal' reason for using a loop.

Are you perhaps trying to search for some value to determine what data is needed?

 

[gmazza76]

Afternoon,

Ok, I am not sure why it loops twice that shouldnt be happening.
What should happen is that the manager's name is entered into the userform ( the Vb you did for me last time ) it checks the password and either says no or go on and use the manager's name entered ( in txtPass ) to search column "C" for the name. If the name is in column C it should then populate the userform. If the name isnt there it should say what is in the MsgBox "Nothing to Update".

I think the "If Cells(i, 1). vlaue" etc ..was for when i had it looping over 3 seperate sheets.

Is there anyway i can delete the row that has been populated into the userform in the load part of the VB or would i need to insert this as part of the submit button on Userform1 when everything is updated?

Cheers
Gavin