Position to UserForm TextBox from OK_Click MsgBox

[wpiet]

in a UserForm with 2 textboxes (UsrID & Psw) & 2 command buttons (OK &
Cancel), my sub OK_Click is as follows:

Private Sub OK_Click()
If Me.UsrID = Empty Then
MsgBox ("You Must Enter A User Name")
Else
If Me.Psw = Empty Then
MsgBox ("You Must Enter A Password")
Else
Module1.UsrID = Me.UsrID.Value
Module1.Psw = Me.Psw.Value
Unload Me
End If
End If
End Sub

If either If condition is true, how can I position back to the offending
textbox for re-entry?
Also, for Cancel, I would like to abort the calling module after verifying
the user's intention of cancelling. Any ideas on this?
Thanks.

 

[Rick Rothstein]

This will handle the first part of your question...

Private Sub OK_Click()
If Len(Me.UsrID) > 0 Then
MsgBox "You Must Enter A User Name"
Me.UsrID.SetFocus
ElseIf Len(Me.Psw) Then
MsgBox "You Must Enter A Password"
Me.Psw.SetFocus
Else
Module1.UsrID = Me.UsrID.Value
Module1.Psw = Me.Psw.Value
Unload Me
End If
End Sub

Notice that I restructured your If..ElseIf..Else..EndIf block a little
bit... I think this structure is "cleaner". Also notice I replaced your
Empty test with a test to make sure no characters exist in the TextBox...
Empty is really for use with Variant variable to indicate an uninitialized
variable, not for use with text in a control. Also, as your code was
written, if the user type in a space character into either or both
TextBoxes, your existing code would accept them.

As for your second part of your question, you will need to pass something
back into Module1 (maybe via the Module1.UsrID, say, set it to a
non-typeable character) and then test for it in back in the Module.
Something like this maybe...

Private Sub Cancel_Click()
Module1.UsrID = Chr$(1)
End Sub

and in the Module1 code, just after you show the UserForm, a test like
this...

If Module1.UsrID = Chr$(1) Then
'<< clean up as necessary and close down your module>>
End If

 

[Chip Pearson]

Use the SetFocus method before your MsgBox. E.g,

Me.UsrID.SetFocus
MsgBox(...)




Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[wpiet]

Excellent. Both suggestions will do what I want.

As an initial step for Cancel, I'm thinking of showing another Userform to
verify the Cancel operation, with a label such as "Are You Sure You Want To
Cancel?" and possibly 2 OptionButtons, labeled "Return To Login" & "Abort."
Do you think that is too messy? This is the first I've ever used UserForms,
so it's a complete learning curve for me. The help you guys give here is
invaluable & greatly appreciated. Thanks.

 

[Chip Pearson]

There's nothing wrong with using a userform, but a MsgBox might
suffice. E.g.,

Dim Result As VbMsgBoxResult
Result = MsgBox("Do you want to cancel?", vbYesNo)
If Result = vbYes Then
' user clicked Yes
Else
' user clicked No
End If

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[wpiet]

Thanks to Rick & Chip for guiding an old COBOL programmer as he gets his feet
wet with VBA.
For the benefit of other newbies searching for similar solutions, here is my
final code:

In UserForm code:
Private Sub Cancel_Click()
Module1.Abt =
MsgBox("Are You Sure You Want To Cancel?", vbYesNo +
vbDefaultButton2)
If Module1.Abt = vbYes Then
Unload Me
Else
Me.UsrID.SetFocus
End If
End Sub

Private Sub OK_Click()
If Len(Trim(Me.UsrID)) = 0 Then
MsgBox ("You Must Enter A User Name")
Me.UsrID.SetFocus
ElseIf Len(Trim(Me.Psw)) = 0 Then
MsgBox ("You Must Enter A Password")
Me.Psw.SetFocus
Else
Module1.UsrID = Me.UsrID.Value
Module1.Psw = Me.Psw.Value
Unload Me
End If
End Sub

In Module1:
In 'Declarations' (before any 'Sub' header). These all receive values
from the UserForm routines:
Public Abt
Public UsrID
Public Psw

Sub Whatever()
< preliminary code>

' Get login from UserForm 'QryLogin'

Abt = vbNo
Load QryLogin
QryLogin.Show
If Abt = vbYes Then ' If
user chose 'Cancel' on UserForm
ActiveWindow.Close SaveChanges:=False
End If

<rest of program if not aborted>
I use the UsrID & Psw, input from the UserForm, in the connection strings
for multiple Query refreshes:
With Selection.QueryTable
.Connection = "ODBC;DSN=Whatever;UID=" _
& UsrID _
& ";PWD=" _
& Psw _
& ";"
.Refresh BackgroundQuery:=False
End With
End Sub