Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Position to UserForm TextBox from OK_Click MsgBox
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. -- Will |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Position to UserForm TextBox from OK_Click MsgBox
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 -- Rick (MVP - Excel) "wpiet" wrote in message ... 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. -- Will |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Position to UserForm TextBox from OK_Click MsgBox
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) On Tue, 17 Feb 2009 13:30:00 -0800, wpiet wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Position to UserForm TextBox from OK_Click MsgBox
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. -- Will "Rick Rothstein" wrote: 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 -- Rick (MVP - Excel) "wpiet" wrote in message ... 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. -- Will |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Position to UserForm TextBox from OK_Click MsgBox
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) On Tue, 17 Feb 2009 14:23:01 -0800, wpiet wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Position to UserForm TextBox from OK_Click MsgBox
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 -- Will "Chip Pearson" wrote: 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) On Tue, 17 Feb 2009 14:23:01 -0800, wpiet wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Position of MsgBox | Excel Programming | |||
MsgBox Position | Excel Programming | |||
MsgBox position | Excel Programming | |||
MsgBox position | Excel Programming | |||
Position of MsgBox | Excel Programming |