User Form Looping Problem

[hotshot5231]

Hey Everyone. I'm new to the forum, and also relatively new to VBA in excel.

I'm working on some VBA with custom user forms that will essentially add ATM logic to a spread sheet (ie user login, account balance, withdrawal, deposit etc..). The custom user forms are throwing me off. I have the below code, and i'm having trouble figuring out how to tell my form named "login" to allow the user 3 input attempts before it closes out.

I think my error has something to do with my loop being inside the "go_click". Maybe it is being re-initialized everytime? Any thoughts?



Private Sub CommandButton1_Click()

Call UserForm_Initialize

End Sub

-------------------------------------------------

Sub Go_Click()
'Declare Variables
Dim Match_USR_PWD As String
Dim Username As Double
Dim Password As String
Dim Dumb_Var_PWD As String
Dim Rng_Fin As Range
Dim count_attempt As Integer
count_attempt = 0

Do While count_attempt < 3

'Concatenate username and password
Match_USR_PWD = frmUsername.Value & frmPassword.Value

If Trim(Match_USR_PWD) <> "" Then
With Sheets("userids").Range("C:C")
Set Rng_Fin = .Find(What:=Match_USR_PWD, _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
If Not Rng_Fin Is Nothing Then
MsgBox ("Welcome.")
Login.Hide
ATM_Main_Menu.Show
Else
Dumb_Var_PWD = MsgBox("Username and password do not match. Please re-enter your username and password", _
vbCritical + vbOKOnly, "No Match")
frmPassword.Value = ""
frmUsername.Value = ""
End If
End With
End If

If count_attempt < 3 Then
count_attempt = count_attempt + 1
Else
Login.Hide
MsgBox ("You're done")
End If
Loop

End Sub

-----------------------------------------------------------------

Private Sub UserForm_Initialize()

frmPassword.Value = ""
frmUsername.Value = ""

End Sub

[Bob Phillips]

Is this a two-form operation, supposed to show the Login form and allow 3 attempts at a password before quitting?

[nst1107]

You are correct about the variable being "reinitialized". Every time the button is clicked, the variable is initialized by the Dim statement, i.e., reset to empty.

Declare count_attempt as a global variable (place the Dim statement at the top of the module, above all subroutines). Keep in mind that a global variable will hold its value until the program closes, so if you intend to use the Login form more than once in a session, you should probably add a line to assign 0 to count_attempt whenever the login is successful.

[hotshot5231]

Is this a two-form operation, supposed to show the Login form and allow 3 attempts at a password before quitting?

That is correct. Sorry for the lack of explanation.

[hotshot5231]

You are correct about the variable being "reinitialized". Every time the button is clicked, the variable is initialized by the Dim statement, i.e., reset to empty.

Declare count_attempt as a global variable (place the Dim statement at the top of the module, above all subroutines). Keep in mind that a global variable will hold its value until the program closes, so if you intend to use the Login form more than once in a session, you should probably add a line to assign 0 to count_attempt whenever the login is successful.
That makes sense, but I'm having a little trouble getting it to work. I'm getting the following error message when i try this code.

Compile Error:
Constants, fixed-length strings, arrays, user-defined types and Declare statements not allowed as Public members of object modules


Global count_attempt As Integer


Sub CommandButton1_Click()

Call UserForm_Initialize

End Sub




Sub Go_Click()

'Declare Variables
Dim Match_USR_PWD As String
Dim Username As Double
Dim Password As String
Dim Dumb_Var_PWD As String
Dim Rng_Fin As Range


Do While count_attempt < 3

'Concatenate username and password
Match_USR_PWD = frmUsername.Value & frmPassword.Value

If Trim(Match_USR_PWD) <> "" Then
With Sheets("userids").Range("C:C")
Set Rng_Fin = .Find(What:=Match_USR_PWD, _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
If Not Rng_Fin Is Nothing Then
MsgBox ("Welcome.")
Login.Hide
ATM_Main_Menu.Show
Else
Dumb_Var_PWD = MsgBox("Username and password do not match. Please re-enter your username and password", _
vbCritical + vbOKOnly, "No Match")
frmPassword.Value = ""
frmUsername.Value = ""
End If
End With
End If

If count_attempt < 3 Then
count_attempt = count_attempt + 1
Else
Login.Hide
MsgBox ("You're done")
End If

Loop


End Sub

Sub UserForm_Initialize()

frmPassword.Value = ""
frmUsername.Value = ""

End Sub

[MaximS]

try to change sub to Private Sub.

[hotshot5231]

try to change sub to Private Sub.

Which sub are you talking about?

[nst1107]

Don't use
Global count_attempt As Integer I don't think Global is a valid statement. What I meant was, simply move your line
Dim count_attempt As Integer out of your sub to the top of the module. That will make it available to all subs in your module, and will prevent it from being reinitialized every time you click the button.

[Bob Phillips]

Global is a valid statement, I use it all the time. It is the same as Public, just is more self-explanatory IMO.

[nst1107]

I guess you're right. When I typed it in real quick in a userform module, it generated the same error as the OP. Why can't Global be used in an object module, but Public can? Aren't they essentially the same statement?

[Bob Phillips]

They are essentially the same the statemnet, in a standard code module, but the context also has to be considered.

A variable declared as Public in a standard code module can be accessed directly by any module anywhere without any qualification. Howevere, whilst a variable declared as Public in a class module, and a userform is just a special type of class module, is now exposed to other modules, it is still a property of that class and has to be qualified by that class instance (Userform1.myVar for instance).

A variable declared as Global is saying it is global, and thus only applies to variables that are global, not variables that are properties of a class. That is why I was saying that I think it is more self-documenting.

[nst1107]

Makes sense. Thanks for the explanation.

[hotshot5231]

Don't useI don't think Global is a valid statement. What I meant was, simply move your lineout of your sub to the top of the module. That will make it available to all subs in your module, and will prevent it from being reinitialized every time you click the button.
Thanks for all the help. I think i'm close. When I use the following. I get an error that highlights the 0 and says "Invalid outside procedure"... any thoughts?


Dim countattempt As Integer
countattempt = 0

[nst1107]

Putcountattempt = 0inside the appropriate sub. You can usePublic Const countattempt As Integer = 0but you are trying to use it as a variable, not a constant.

[GTO]

edit: deleted/answered

[hotshot5231]

Putcountattempt = 0inside the appropriate sub. You can usePublic Const countattempt As Integer = 0but you are trying to use it as a variable, not a constant.

Well I need to start it at something so that my loop will function properly. (see code above). Or am I looking at it wrong?

[nst1107]

An integer variable is initialized to 0. So, you do not needcountattempt = 0except to reset the variable.

[Bob Phillips]

An integer variable is initialized to 0. So, you do not needcountattempt = 0except to reset the variable.

But it is very good practice to do so, not all languages/compilers work the same.

[nst1107]

But it is very good practice to do so, not all languages/compilers work the same.Bob knows a lot more than I do.

Take a look at this code and see if it makes sense.Option Explicit
Global longtype As Long
Sub example()
longtype = 0 '<I just learned this is good practice!
MsgBox "Variable = " & longtype
End Sub