Message Box if Values do not meet

[adam]

Hi anyone,

How could a vba code be written that would pop up a message saying "this code is incorrect please re-do" ; if the values on Sheet1 does not match with that of the data with columns in cell A2 and B2.

Lets say for example the column A2 of sheet2 has "A" and B2 has "B".
The user types "A" in cell "J2 of the sheet1 and "O" in cell "J4". The Vba code at this instance would show a messagebox "this code is incorrect please re-do"

Thanks in advance.

[HansV]

When should the code run?

[StuartR]

Would it not be better to use simple validation rules to do this, rather than VBA code.

Have a look at this page to see if it can do what you need.

[adam]

Thanks for the replies. Here's a working code.

Code: Select all

Sub LoginButton_Click()
'   Checking password and logging in

    LoginName = Sheets("LoginPage").Range("J18")
    UserPassword = Sheets("StaffList").Range(Sheets("StaffList").Cells. _
                   Find(What:=LoginName).Address).Offset(0, 1).Value

    With Worksheets("LoginPage").Range("J20")

        If .Value = "" And UserPassword <> "" Then
            MsgBox "Please enter pasword and / or username"
            End
            Exit Sub
        End If

        If .Value = UserPassword Then
            MsgBox "Dear " & LoginName & vbCrLf & _
                   "Thank You For Logging In", vbInformation, "Thank You"
    
            Application.ScreenUpdating = False

            With ActiveWindow
                .DisplayHorizontalScrollBar = True
                .DisplayVerticalScrollBar = True
                .DisplayWorkbookTabs = True
            End With
            Application.EnableCancelKey = xlInterrupt
            Application.ScreenUpdating = True
        Else
            MsgBox "Incorrect name or password" & vbCrLf & _
                   "Please Try Again", vbCritical, "Error"
                   Exit Sub
        End If
    End With
Sheets("Homepage").Activate
End Sub

How could I format the cell "J20" so that when the user types the password it appears as asterix?

[HansV]

You shouldn't use a worksheet cell to store a password. Even if you mask it, one can create a formula that refers to the cell and see the password. I'd use a userform instead. A text box on a userform has a PasswordChar property. If you set this to *, every character will be displayed as an * but the value entered by the user will be available to your VBA code.

[adam]

I'm trying to assigne each user a password upon user creation. Meaning, the user will create their own password and save it on the workbook. But is it possible to save the password in the VBA module?

Suppose if the workbook users want to add a new user and assigned a password to each how could this be done without saving the password to the workbook sheet?

If the password is to be placed in the VBA code, they should be preassigned right?

[HansV]

You can't save a password in the VBA code. You could use a separate, very hidden worksheet to store the password.

[StuartR]

It is very poor practice to store unencrypted passwords anywhere.

The correct approach is to encrypt the password using a one way algorithm. You can then store the encrypted password and when you need to authenticate the user you encrypt the password they supply, using the same algorithm, and compare this with the stored encrypted password.

There are a number of well known algorithms for doing this one way encryption, a quick search just now turned up this implementation of an algorithm called crypt3.

[adam]

At Hans; yes that is what I have done. What I'm asking is how to format the cell "J20" so that when the user types letters it appears as asterix?

[HansV]

You can make the cell display **** AFTER the user has confirmed the entry, not while the user is typing. You could hide the cell by placing an asterix on top of it:

x139.png

But the password will still be visible in the formula bar, as you can see in the screenshot. Please see Stuart's suggestion.

[adam]

A change of mind is being made to write the password in userform. Having this in mind I'm trying to modify the following code so that it shows of a message if the text that is being entered in the NewUserNameBox already exists in column A of "MySheet"

Code: Select all

Private Sub NewUserNameBox_AfterUpdate()
If WorksheetFunction.CountIf(Sheets("MySheet").Cells, NewUserNameBox.Value) = 1 Then
        Me.Hide
        MsgBox "The name is in use" & vbCrLf & _
               "Please use another name", vbCritical, "Username Error"
        Me.Show
        NewUserNameBox.Value = ""
        End
    End If
End Sub

Any help on this would be appreciated.

[HansV]

The code currently looks in all cells on MySheet: Sheets("MySheet").Cells.
It shouldn't be difficult to change that to column A.

[adam]

Thanks for the suggestion. On the same form I'm trying to implement the following code so that when the two passwords does not match it would hide the form and show a message box. But this code does not seem to be hiding the form and showing the messagebox. Instead it shows the message box with the form in backgound.What may be the reason for this?

Code: Select all

Private Sub ConfirmBox_AfterUpdate()
Dim WS As Worksheet
Set WS = Worksheets("USERSHEET")
Dim password As String
password = SetPasswordBox.Text
secondpassword = ConfirmBox.Text
If password = secondpassword Then
Me.Hide
Else
MsgBox "Passwords do not match, please try again", vbExclamation
End If
End Sub

[HansV]

In your code, you hide the userform if the passwords match.
If the passwords do not match (in the Else part), you display a message box but do not hide the userform.