Userform SetFocus with Exit and Cancel

[Neal]

Dear All,

I have a userform with a couple of textboxes and an OK and Cancel button at
the bottom of the form. After consulting this news group, I found the Exit
event (I was using the AfterUpdate event before, but wanted the setfocus to
be in a certain textbox.) For validation purposes, I have set up the Exit
event as you can see in the following:

Private Sub SymbolBox_Exit(ByVal cancel As MSForms.ReturnBoolean)
'
Dim RngFound As Range, SymbolRange As Range 'SellRange as Range
Dim SellSymbol As String ' LstRow As Integer
If SymbolBox.Value = "ABC" Then
If Not IsNumeric(SymbolBox.Value) Then
SymbolBox.Value = UCase(SymbolBox.Value)
SellBox.SetFocus
Else
MsgBox "Need to type in a valid symbol"
cancel = True
SymbolBox.Text = ""
SymbolBox.SetFocus
End If
Else
MsgBox "Need to enter a symbol"
cancel = True
SymbolBox.Text = ""
SymbolBox.SetFocus
'Exit Sub
End If

End Sub

The problem is if the user does not enter any data, but wants to exit the
form and clicks on the Cancel button, nothing happens. The userform does not
close. How can I get the userform to close? Thanks in advance.

Neal

 

[Dave Peterson]

I think I'd drop the msgboxes and use a label on the userform.

And if you change the cancelbutton.takefocusonclick to false, they can click the
cancel button.



Option Explicit

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub SymbolBox_Exit(ByVal cancel As MSForms.ReturnBoolean)
'
Dim RngFound As Range, SymbolRange As Range 'SellRange as Range
Dim SellSymbol As String ' LstRow As Integer

me.label1.caption = ""
If SymbolBox.Value = "ABC" Then
If Not IsNumeric(SymbolBox.Value) Then
SymbolBox.Value = UCase(SymbolBox.Value)
sellbox.SetFocus
Else
Me.Label1.Caption = "Need to type in a valid symbol"
cancel = True
SymbolBox.Text = ""
SymbolBox.SetFocus
End If
Else
Me.Label1.Caption = "Need to enter a symbol"
cancel = True
SymbolBox.Text = ""
SymbolBox.SetFocus
'Exit Sub
End If

End Sub

Private Sub UserForm_initialize()
Me.Label1.Caption = ""
Me.CommandButton1.TakeFocusOnClick = False
End Sub

I'm not sure what you were doing in your test code, though.

 

[Neal]

Dave,

Thanks for the advice. I will try it. What I was trying to do in my code
was test to see if the user entered the right three digit code. If it was
not correct or nothing was entered or it was numeric, then I wanted the
messagebox to popup and say that the user needed to enter a three digit
alphanumeric code. When the user hit OK on the msgbox, the setfocus would
return to the SymbolBox. I will try the labels. Do the labels look like the
message boxes? Thanks.

Neal

 

[Dave Peterson]

No. The labels look like textboxes with a grey background(?).

Ahhh. You'll see it when you try it.