user form unloads on cancel command but not on potentially correct submitted data-CodePudding

My vba is quite basic and this is my first user form I've ever generated so I've been testing it as I add complexity. the form is designed so I can feed in login credentials into a query string to pull a request from our data warehouse straight into excel rather than going through the website itself.

so I created a rather simple user form with two text boxes (username and password) and a login and cancel button. the user form would then feed this information into the class object that will feed the wider request module (the module has been tested with hard coded values and works).

I set up a simple check on login that data had been provided (there is a detailed error loop for incorrect details in the main code) that just checks if each of the two elements have some text in them and if they both do should unload the form so it is not still on screen after the rest of data request runs.

However, I seem to have two issues with my code currently first it will not run the if checks as separate items , so if the username passes the if statement then the rest of the code seems to stop. second if i have populated both fields (which should satisfy the IF checks) the form doesn't unload. I have copied in my code for the login button below I can't obviously see anything wrong with it as the end if all happen before the next if starts but any help to explain why this isn't working would be great. the items called in the len functions are just the names of the fields in the user form, if anything else is unclear just shout.

    Private Sub Login_Click()

'check username is present
Dim corect_details As Integer
Dim uname As Integer
Dim pswrd As Integer
 pswrd = 0
uname = 0
If Len(Username) = 0 Then
    MsgBox "please enter your user name", vbOKOnly
    Else
     uname = 1
    Exit Sub
    End If
If Len(Password) = 0 Then
MsgBox "please enter a password", vbOKOnly
    Exit Sub
    Else
   pswrd = 1
    End If
 details = pswrd   uname
MsgBox details
    If details = 2 Then
    Unload Me
    Else
    End If

End Sub

CodePudding user response：

The error is here:

If Len(Username) = 0 Then
    MsgBox "please enter your user name", vbOKOnly
Else
    uname = 1
    Exit Sub 'should be above
End If

The Else branch is the correct option so you shouldn't exit the sub. But, how about using a Select Case statement?

Private Sub Login_Click()

    Select Case True
        Case Len(UserName) = 0:
            MsgBox "please enter your user name", vbOKOnly
        
        Case Len(Password) = 0:
            MsgBox "please enter a password", vbOKOnly
        
        Case Else:
            MsgBox "all good"
    End Select

End Sub

CodePudding user response：

Below is an example of a form a made a few years back. I usually do the variable checks on the procedure side not the form side, but as @Kostas K. mentioned you 'Exit Sub' when someone correctly enters their username. Also exit sub does not stop the remaining procedure to finish and it will continue normally, that's why it's better to do this logic outside the form unless you want to use 'End' instead of 'Exit Sub' which exits everything not just the current sub.

Private Declare PtrSafe Function GetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Private Sub btnCancel_Click()
Unload Me
End
End Sub

Private Sub Login_Click()
Me.Hide
Me.Tag = vbOK
End Sub

Private Sub UserForm_Activate()
frmLogin.txtUserID.SetFocus
frmLogin.txtUserID = ReturnUserName()
End Sub

Private Sub UserForm_Initialize()
Me.StartUpPosition = 0
Me.Left = Application.Left   (0.5 * Application.Width) - (0.5 * Me.Width)
Me.Top = Application.Top   (0.5 * Application.Height) - (0.5 * Me.Height)
End Sub

Public Function ReturnUserName()
'Return the users name
Dim strUser As String, x As Integer
strUser = Space$(256)
x = GetUserName(strUser, 256)
strUser = RTrim(strUser)
ReturnUserName = Left(strUser, Len(strUser) - 1)
End Function