user form error

niailmar

New member
Joined
Apr 4, 2012
Messages
30
Reaction score
0
Points
0
Hello everyone.
I've one problem about creating user form. I found an example about creating password and implement it into my system. And in another sheet , I already create a list for username and password as reference.


Refer from image attached, after user click the button of Guarantee Letter, the login (password required) form will popup. The problem is the selected username a not showing.


The second is, login (new password) also have a problem like attached image. I'm not sure whether I put the wrong reference code or using the wrong selected object.


Please help me. Thank so muchproblem.jpg
 

maninweb

New member
Joined
Mar 22, 2011
Messages
88
Reaction score
0
Points
0
Location
Germany
Website
excel-translator.de
Excel Version(s)
2010, 2016, 2016 Insider
Hi niailmar...

I think, posting the code or uploading (if possible) a sample file, would be useful.

Regards :)
 

niailmar

New member
Joined
Apr 4, 2012
Messages
30
Reaction score
0
Points
0
code for password required
Code:
Private Sub Cancel_Click()
'Sheets("Protected").Visible = xlVeryHidden
MsgBox "Password Not Entered.  Workbook will close!"
Unload Me
ThisWorkbook.Close
End Sub


Private Sub EnterPassword_Click()
Dim password As String
password = PasswordInput.Text




If password = WorksheetFunction.VLookup(UserList.Value, Range("Users_List"), 2, 0) Then
'Sheets("Protected").Visible = xlVeryHidden
'Sheets("Blank").Visible = xlVeryHidden
MsgBox "Password Accepted"
Unload Me
Else
'Sheets("Protected").Visible = xlVeryHidden
'Sheets("Blank").Visible = xlVeryHidden
MsgBox "Password Not Accepted, Workbook will close"
Unload Me
ThisWorkbook.Close
End If






End Sub


Private Sub UpdatePassword_Click()
Dim password As String
password = PasswordInput.Text
If password = WorksheetFunction.VLookup(UserList.Value, Range("Users_List"), 2, 0) Then
User = UserList.Value
Sheets("Blank").Range("A1").Value = User
Unload Me
UserForm2.Show
Else
MsgBox "Passwords do not match, please try again"
End If






End Sub






Private Sub UserForm_Terminate()
Dim password As String
password = PasswordInput.Text


If PasswordInput.Text = "" Or UserList.Value = "" Then


MsgBox "must use this form, workbook will close"
ThisWorkbook.Close
ElseIf password = WorksheetFunction.VLookup(UserList.Value, Range("Users_List"), 2, 0) Then






End If
End Sub


Private Sub UserList_Change()


End Sub


code for new password
Code:
Private Sub CommandButton1_Click()


Dim password As String


password = TextBox1.Text
secondpassword = TextBox2.Text


If password = secondpassword Then


With Worksheets("Protected").Range("Users")
    Set c = .Find(Sheets("Blank").Range("A1").Value, LookIn:=xlValues)
    If Not c Is Nothing Then
        Sheets("Protected").Range(c.Address).Offset(0, 1).Value = password
    Sheets("Protected").Visible = xlVeryHidden
    Sheets("Blank").Visible = xlVeryHidden
MsgBox "Password Updated"


Unload Me
Sheets("Blank").Range("A1").Value = ""
    End If
End With


Else
MsgBox "Passwords do not match, please try again"
End If








End Sub






Private Sub TextBox1_Change()


End Sub


Private Sub TextBox2_Change()


End Sub


Private Sub UserForm_Terminate()
Dim User As String


User = Sheets("Blank").Range("A1").Value


If TextBox1.Value = WorksheetFunction.VLookup(User, Range("Users_List"), 2, 0) Then


Else
MsgBox "must use this form, workbook will close"
ThisWorkbook.Close
End If
End Sub


this code, I've put at this workbook.


Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Blank").Visible = xlSheetVisible
End Sub


Private Sub Workbook_Open()
If Sheets("Blank").Visible <> xlSheetVisible Then
Sheets("Blank").Visible = xlSheetVisible
End If
Sheets("Blank").Select
Application.ScreenUpdating = False
Application.DisplayAlerts = False


Sheets("Protected").Visible = xlSheetVisible
UserForm1.Show
End Sub


the highlighted code that occur an error serving
debug is...
If TextBox1.Value = WorksheetFunction.VLookup(User, Range("Users_List"), 2, 0) Then




thank you..
 

maninweb

New member
Joined
Mar 22, 2011
Messages
88
Reaction score
0
Points
0
Location
Germany
Website
excel-translator.de
Excel Version(s)
2010, 2016, 2016 Insider
Hi niailmar...

the error probably occurs because you are not checking if the username in from Sheets("Blank").Range("A1").Value also exists in the named range "Users_List".
I simulated the userform (however I don't really know the structure of your workbook) and added some code for searching if the user name is in the range.
However, you may eventually change the code.
Code:
Private Sub UserForm_Terminate()
  
  Dim blnClose As Boolean
  Dim strUser  As String
  Dim rngFind  As Range
  
' Initialize...
  
  blnClose = False
  strUser = Sheets("Blank").Range("A1").Value

' Check if in Range

  With Range("Users_List")
  
'   Try...
    
    Set rngFind = .Find(strUser, LookIn:=xlValues)
    
'   Check...
    
    If rngFind Is Nothing Then
      
      MsgBox "The specified user name is not in the list"
      
      blnClose = True
      
    Else
      
'     Now verify the password...
      
      If TextBox1.Value <> WorksheetFunction.VLookup(strUser, Range("Users_List"), 2, 0) Then
        
        MsgBox "must use this form, workbook will close"
        
        blnClose = True
        
      End If
      
    End If
    
  End With
  
' Close...
  
  If blnClose Then
    
    ThisWorkbook.Close
    
  End If
  
End Sub

Regards :)
 
Top