skidda420

New Member
Joined
Jun 7, 2018
Messages
23
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I am working on a Userform that allows users to sign into projects using their login and password credential. However I'm running into a situation where sometimes my worksheet_change events work and other times they don't and it's allowing for the same username to be entered when creating their username. I am also using this worksheet_change for their initials as well so that people with the same initials will have to come up with something else if those initials are already in use. I have this VBA on three different sheets for three different workstations. Like I said sometimes it goes off without a hitch other times it just doesn't work, any help is appreciated. When successfully implemented it goes to a 'create your password' userform which works charming, however it's getting to that point that proves to be a little bit of a struggle.


VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Whoa

Application.EnableEvents = False

If Range("I2") = "Not Logged In" Then              '<---when no one is logged in, users are directed to the main login page
Exit Sub
End If

If WorksheetFunction.CountIf(Range("A5:A100"), Target) > 1 Then
MsgBox "Sorry, That Username Is Already In Use, Please Provide A New Username.", vbInformation, "Username Already In Use"
Target.Value = ""
End If
If Target.Value = "" Then
Target.Offset(0, 3).Value = ""
Range("L1") = False
Unload PWUserForm                     '<------for some reason it wanted to load the password userform so, had to tell it to unload it
End If
If WorksheetFunction.CountIf(Range("A5:A100"), Target) = 1 Then
Range("L1") = True
End If

If WorksheetFunction.CountIf(Range("D5:D100"), Target) > 1 Then
MsgBox "Sorry, Those Initials Are Already Being Used.", vbInformation, "Initials Already In Use"
Target.Value = ""
End If
If Target.Value = "" Then
Target.Offset(0, -3).Value = ""
Range("M1") = False
Unload PWUserForm
End If

If WorksheetFunction.CountIf(Range("D5:D100"), Target) = 1 Then
Range("M1") = True
End If

Letscontinue:
Application.EnableEvents = True
Exit Sub

Whoa:
Resume Letscontinue
End Sub
 

Attachments

  • login.JPG
    login.JPG
    141.4 KB · Views: 6
Last edited by a moderator:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,863
Messages
5,766,821
Members
425,379
Latest member
thedoctor00

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
Top