Hi

I'm trying to set focus back to a textbox after exiting the textbox, if the
condition isn't met.

I found a similar example in an earlier thread by jimec74, titled "Using
SetFocus with Frames", which was said to work. But when I tested it myself in
Excel 2003, the SetFocus doesn't seem to fire (i.e. focus still jumps to the
next textbox). Is this just me?


Sub Txt_StartValue_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim Msg, Title, Response

If IsNumeric(Txt_StartValue.Value) = False Then
Msg = "You must enter a number"
Title = "Non-numeric Value"
Response = MsgBox(Msg,16,Title)
Txt_StartValue.SetFocus
End If

End Sub

Hi,

If you use

Txt_StartValue.Value = ""
Cancel = True

The incorrect entry will be cleared and the focus will be set back to the
Textbox

Mike

"Sam Kuo" wrote:

Hi

I'm trying to set focus back to a textbox after exiting the textbox, if the
condition isn't met.

I found a similar example in an earlier thread by jimec74, titled "Using
SetFocus with Frames", which was said to work. But when I tested it myself in
Excel 2003, the SetFocus doesn't seem to fire (i.e. focus still jumps to the
next textbox). Is this just me?


Sub Txt_StartValue_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim Msg, Title, Response

If IsNumeric(Txt_StartValue.Value) = False Then
Msg = "You must enter a number"
Title = "Non-numeric Value"
Response = MsgBox(Msg,16,Title)
Txt_StartValue.SetFocus
End If

End Sub

Hi,

Right click your sheet tab, view code and paste this in

Sub extractnumbers()
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim myrange As Range, C As Range, Outstring As String
Set RegExp = CreateObject("vbscript.RegExp")
RegExp.Global = True
For x = 1 To 2
If x = 1 Then
RegExp.Pattern = "\d"
Else
RegExp.Pattern = "\D"
End If
Set myrange = ActiveSheet.Range("a1:a20") 'change to suit
For Each C In myrange
Outstring = ""
Set Collection = RegExp.Execute(C.Value)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
Next
C.Offset(0, x) = Outstring
Next
Next
End Sub

Mike

"Sam Kuo" wrote:

Hi

I'm trying to set focus back to a textbox after exiting the textbox, if the
condition isn't met.

I found a similar example in an earlier thread by jimec74, titled "Using
SetFocus with Frames", which was said to work. But when I tested it myself in
Excel 2003, the SetFocus doesn't seem to fire (i.e. focus still jumps to the
next textbox). Is this just me?


Sub Txt_StartValue_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim Msg, Title, Response

If IsNumeric(Txt_StartValue.Value) = False Then
Msg = "You must enter a number"
Title = "Non-numeric Value"
Response = MsgBox(Msg,16,Title)
Txt_StartValue.SetFocus
End If

End Sub

oops,

wrong thread

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in

Sub extractnumbers()
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim myrange As Range, C As Range, Outstring As String
Set RegExp = CreateObject("vbscript.RegExp")
RegExp.Global = True
For x = 1 To 2
If x = 1 Then
RegExp.Pattern = "\d"
Else
RegExp.Pattern = "\D"
End If
Set myrange = ActiveSheet.Range("a1:a20") 'change to suit
For Each C In myrange
Outstring = ""
Set Collection = RegExp.Execute(C.Value)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
Next
C.Offset(0, x) = Outstring
Next
Next
End Sub

Mike

"Sam Kuo" wrote:

Hi

I'm trying to set focus back to a textbox after exiting the textbox, if the
condition isn't met.

I found a similar example in an earlier thread by jimec74, titled "Using
SetFocus with Frames", which was said to work. But when I tested it myself in
Excel 2003, the SetFocus doesn't seem to fire (i.e. focus still jumps to the
next textbox). Is this just me?


Sub Txt_StartValue_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim Msg, Title, Response

If IsNumeric(Txt_StartValue.Value) = False Then
Msg = "You must enter a number"
Title = "Non-numeric Value"
Response = MsgBox(Msg,16,Title)
Txt_StartValue.SetFocus
End If

End Sub

Hi Mike

Thanks for your reply.

I didn't use Cancel = True because it locks evertything else until the user
enters a value, but in my case, I'd like to allow the user to be able to go
away and look for the correct value if they have a false entry.

Is there another way around this problem?

Sam

"Mike H" wrote:

Hi,

If you use

Txt_StartValue.Value = ""
Cancel = True

The incorrect entry will be cleared and the focus will be set back to the
Textbox

Mike

"Sam Kuo" wrote:

Hi

I'm trying to set focus back to a textbox after exiting the textbox, if the
condition isn't met.

I found a similar example in an earlier thread by jimec74, titled "Using
SetFocus with Frames", which was said to work. But when I tested it myself in
Excel 2003, the SetFocus doesn't seem to fire (i.e. focus still jumps to the
next textbox). Is this just me?


Sub Txt_StartValue_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim Msg, Title, Response

If IsNumeric(Txt_StartValue.Value) = False Then
Msg = "You must enter a number"
Title = "Non-numeric Value"
Response = MsgBox(Msg,16,Title)
Txt_StartValue.SetFocus
End If

End Sub

On Sun, 3 Aug 2008 13:54:00 -0700, Sam Kuo
wrote:

Hi Mike

Thanks for your reply.

I didn't use Cancel = True because it locks evertything else until the user
enters a value, but in my case, I'd like to allow the user to be able to go
away and look for the correct value if they have a false entry.

"Mike H" wrote:

Txt_StartValue.Value = ""
Cancel = True

"Sam Kuo" wrote:

Sub Txt_StartValue_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim Msg, Title, Response

If IsNumeric(Txt_StartValue.Value) = False Then
Msg = "You must enter a number"
Title = "Non-numeric Value"
Response = MsgBox(Msg,16,Title)
Txt_StartValue.SetFocus
End If

End Sub


If Len(Me.Txt_StartValue.Text) 0 And _
Not IsNumeric(Me.Txt_StartValue.Text) Then

This will allow the user to enter a number or a blank. Then you can use the
Cancel variable to keep it in the textbox, but allow the user to move out of
it if it's blank.

Another option is to set the focus elsewhere, then back to the box you
want.

Me.Txt_SomeOther.SetFocus
Me.Txt_StartValue.SetFocus

I've had some luck doing that.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

That's brilliant! Thanks for Mike.

"Dick Kusleika" wrote:

On Sun, 3 Aug 2008 13:54:00 -0700, Sam Kuo
wrote:

Hi Mike

Thanks for your reply.

I didn't use Cancel = True because it locks evertything else until the user
enters a value, but in my case, I'd like to allow the user to be able to go
away and look for the correct value if they have a false entry.

"Mike H" wrote:

Txt_StartValue.Value = ""
Cancel = True

"Sam Kuo" wrote:

Sub Txt_StartValue_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim Msg, Title, Response

If IsNumeric(Txt_StartValue.Value) = False Then
Msg = "You must enter a number"
Title = "Non-numeric Value"
Response = MsgBox(Msg,16,Title)
Txt_StartValue.SetFocus
End If

End Sub


If Len(Me.Txt_StartValue.Text) 0 And _
Not IsNumeric(Me.Txt_StartValue.Text) Then

This will allow the user to enter a number or a blank. Then you can use the
Cancel variable to keep it in the textbox, but allow the user to move out of
it if it's blank.

Another option is to set the focus elsewhere, then back to the box you
want.

Me.Txt_SomeOther.SetFocus
Me.Txt_StartValue.SetFocus

I've had some luck doing that.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

I mean thanks Dick and Mike :-)


"Dick Kusleika" wrote:

On Sun, 3 Aug 2008 13:54:00 -0700, Sam Kuo
wrote:

Hi Mike

Thanks for your reply.

I didn't use Cancel = True because it locks evertything else until the user
enters a value, but in my case, I'd like to allow the user to be able to go
away and look for the correct value if they have a false entry.

"Mike H" wrote:

Txt_StartValue.Value = ""
Cancel = True

"Sam Kuo" wrote:

Sub Txt_StartValue_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim Msg, Title, Response

If IsNumeric(Txt_StartValue.Value) = False Then
Msg = "You must enter a number"
Title = "Non-numeric Value"
Response = MsgBox(Msg,16,Title)
Txt_StartValue.SetFocus
End If

End Sub


If Len(Me.Txt_StartValue.Text) 0 And _
Not IsNumeric(Me.Txt_StartValue.Text) Then

This will allow the user to enter a number or a blank. Then you can use the
Cancel variable to keep it in the textbox, but allow the user to move out of
it if it's blank.

Another option is to set the focus elsewhere, then back to the box you
want.

Me.Txt_SomeOther.SetFocus
Me.Txt_StartValue.SetFocus

I've had some luck doing that.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

If you have an "ok" button on your userform, you could keep it disabled until
everything is the way you want.

Here's a sample that I saved from a previous post:

You could have a subroutine that checks all the input to see if it's valid
before you enable the combobox.

I made a small userform with a combobox, textbox and two commandbuttons. I
wanted to make sure that there was something in the textbox and something in the
combobox before enabling the commandbutton2 button.

Option Explicit
Private Sub ComboBox1_Change()
Call CheckInput
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub TextBox1_Change()
Call CheckInput
End Sub
Private Sub UserForm_Initialize()
With Me.ComboBox1
.AddItem "A"
.AddItem "B"
.AddItem "C"
.ListIndex = -1
.Style = fmStyleDropDownList
End With
Me.CommandButton2.Enabled = False
End Sub
Private Sub CheckInput()

Dim Ok As Boolean

Ok = True
If Me.ComboBox1.ListIndex < 0 Then
Ok = False
ElseIf Me.TextBox1.Value = "" Then
Ok = False
End If

Me.CommandButton2.Enabled = Ok

End Sub


Sam Kuo wrote:

Hi Mike

Thanks for your reply.

I didn't use Cancel = True because it locks evertything else until the user
enters a value, but in my case, I'd like to allow the user to be able to go
away and look for the correct value if they have a false entry.

Is there another way around this problem?

Sam

"Mike H" wrote:

Hi,

If you use

Txt_StartValue.Value = ""
Cancel = True

The incorrect entry will be cleared and the focus will be set back to the
Textbox

Mike

"Sam Kuo" wrote:

Hi

I'm trying to set focus back to a textbox after exiting the textbox, if the
condition isn't met.

I found a similar example in an earlier thread by jimec74, titled "Using
SetFocus with Frames", which was said to work. But when I tested it myself in
Excel 2003, the SetFocus doesn't seem to fire (i.e. focus still jumps to the
next textbox). Is this just me?


Sub Txt_StartValue_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim Msg, Title, Response

If IsNumeric(Txt_StartValue.Value) = False Then
Msg = "You must enter a number"
Title = "Non-numeric Value"
Response = MsgBox(Msg,16,Title)
Txt_StartValue.SetFocus
End If

End Sub


--

Dave Peterson

Thanks Dave.

For this particular textbox I have, it's probably easier for me to have the
validation done upon changing/exiting. But I can see the benefits of applying
your suggestion to my other controls input check.

Cheers
Sam


"Dave Peterson" wrote:

If you have an "ok" button on your userform, you could keep it disabled until
everything is the way you want.

Here's a sample that I saved from a previous post:

You could have a subroutine that checks all the input to see if it's valid
before you enable the combobox.

I made a small userform with a combobox, textbox and two commandbuttons. I
wanted to make sure that there was something in the textbox and something in the
combobox before enabling the commandbutton2 button.

Option Explicit
Private Sub ComboBox1_Change()
Call CheckInput
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub TextBox1_Change()
Call CheckInput
End Sub
Private Sub UserForm_Initialize()
With Me.ComboBox1
.AddItem "A"
.AddItem "B"
.AddItem "C"
.ListIndex = -1
.Style = fmStyleDropDownList
End With
Me.CommandButton2.Enabled = False
End Sub
Private Sub CheckInput()

Dim Ok As Boolean

Ok = True
If Me.ComboBox1.ListIndex < 0 Then
Ok = False
ElseIf Me.TextBox1.Value = "" Then
Ok = False
End If

Me.CommandButton2.Enabled = Ok

End Sub


Sam Kuo wrote:

Hi Mike

Thanks for your reply.

I didn't use Cancel = True because it locks evertything else until the user
enters a value, but in my case, I'd like to allow the user to be able to go
away and look for the correct value if they have a false entry.

Is there another way around this problem?

Sam

"Mike H" wrote:

Hi,

If you use

Txt_StartValue.Value = ""
Cancel = True

The incorrect entry will be cleared and the focus will be set back to the
Textbox

Mike

"Sam Kuo" wrote:

Hi

I'm trying to set focus back to a textbox after exiting the textbox, if the
condition isn't met.

I found a similar example in an earlier thread by jimec74, titled "Using
SetFocus with Frames", which was said to work. But when I tested it myself in
Excel 2003, the SetFocus doesn't seem to fire (i.e. focus still jumps to the
next textbox). Is this just me?


Sub Txt_StartValue_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim Msg, Title, Response

If IsNumeric(Txt_StartValue.Value) = False Then
Msg = "You must enter a number"
Title = "Non-numeric Value"
Response = MsgBox(Msg,16,Title)
Txt_StartValue.SetFocus
End If

End Sub


--

Dave Peterson

Hi Dick

I just notice with both options you suggested, the message box prompts only
when the focus is set to another control in the SAME frame or page (i.e. the
same tab list) after exiting the validation textbox.

e.g. if I exit the validation textbox, say by clicking a control NOT in the
same tab list as the textbox (for example, a different mutipage tab or
another textbox in a different frame), the message box doesn't really prompt
until I click one of the controls in the same tab list as the validation
textbox.

I wonder if there is workaround so the message box fires after exiting the
validation textbox, even if the focus was set to a control in a different tab
list?
(ps. Textbox Change event may not work because of the required validation
condition, which needs the textbox value to be between 50 and 130).

Sam

Private Sub txt1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim Msg, Title, Response
If IsNumeric(txt1.Value) = False Then
If txt1.Value = "" Then Exit Sub
Msg = "You must enter a number"
Title = "Non-numeric Value"
Response = MsgBox(Msg, 16, Title)
txt1.Value = ""
Cancel = True
Exit Sub
End If

If txt1.Value < 50 Or txt1.Value 130 Then
MsgBox "Please enter a value between 50 and 130", _
vbOKOnly + vbExclamation, "Invalid Input"
txt1.Value = ""
Cancel = True
End If
End Sub


"Dick Kusleika" wrote:

On Sun, 3 Aug 2008 13:54:00 -0700, Sam Kuo
wrote:

Hi Mike

Thanks for your reply.

I didn't use Cancel = True because it locks evertything else until the user
enters a value, but in my case, I'd like to allow the user to be able to go
away and look for the correct value if they have a false entry.

"Mike H" wrote:

Txt_StartValue.Value = ""
Cancel = True

"Sam Kuo" wrote:

Sub Txt_StartValue_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim Msg, Title, Response

If IsNumeric(Txt_StartValue.Value) = False Then
Msg = "You must enter a number"
Title = "Non-numeric Value"
Response = MsgBox(Msg,16,Title)
Txt_StartValue.SetFocus
End If

End Sub


If Len(Me.Txt_StartValue.Text) 0 And _
Not IsNumeric(Me.Txt_StartValue.Text) Then

This will allow the user to enter a number or a blank. Then you can use the
Cancel variable to keep it in the textbox, but allow the user to move out of
it if it's blank.

Another option is to set the focus elsewhere, then back to the box you
want.

Me.Txt_SomeOther.SetFocus
Me.Txt_StartValue.SetFocus

I've had some luck doing that.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

On Mon, 4 Aug 2008 17:25:01 -0700, Sam Kuo
wrote:

Hi Dick

I just notice with both options you suggested, the message box prompts only
when the focus is set to another control in the SAME frame or page (i.e. the
same tab list) after exiting the validation textbox.

e.g. if I exit the validation textbox, say by clicking a control NOT in the
same tab list as the textbox (for example, a different mutipage tab or
another textbox in a different frame), the message box doesn't really prompt
until I click one of the controls in the same tab list as the validation
textbox.

I wonder if there is workaround so the message box fires after exiting the
validation textbox, even if the focus was set to a control in a different tab
list?

Frames and Multipages do complicate things, don't they. I have two other
suggestions and you're not going to like either:

1. Don't use Frames or Multipages.
2. Don't try to keep the focus in the textbox.

For #2, I would change the backcolor of the textbox for invalid data and
change the tooltip to describe the situation, but still allow the user to
navigate away. Then prevent the user from doing anything substantial (like
click a Save button) until the data is valid.

Bonus suggestion: Don't use a textbox. Maybe a spinner or slider control
won't exhibit the same quirkiness.

Good luck,
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

haha, I like your sense of humor.

1. I don't think I can afford to change the look of my form now. And even if
I do, chances are I'll be asking more questions here.
2. This was what I did before (i.e. change backcolor with explaination
label), but there's just no room to have such label next to some validation
textboxes in my case.

Well, it's a pitty that what I thought would be a good way of presenting a
form doesn't seem to be feasible...But thanks for your input. Really
appreciated.

ps. What about using the textbox Change event? Would that work? I've just
posted that question in a new thread some time ago.

Sam

"Dick Kusleika" wrote:

On Mon, 4 Aug 2008 17:25:01 -0700, Sam Kuo
wrote:

Hi Dick

I just notice with both options you suggested, the message box prompts only
when the focus is set to another control in the SAME frame or page (i.e. the
same tab list) after exiting the validation textbox.

e.g. if I exit the validation textbox, say by clicking a control NOT in the
same tab list as the textbox (for example, a different mutipage tab or
another textbox in a different frame), the message box doesn't really prompt
until I click one of the controls in the same tab list as the validation
textbox.

I wonder if there is workaround so the message box fires after exiting the
validation textbox, even if the focus was set to a control in a different tab
list?

Frames and Multipages do complicate things, don't they. I have two other
suggestions and you're not going to like either:

1. Don't use Frames or Multipages.
2. Don't try to keep the focus in the textbox.

For #2, I would change the backcolor of the textbox for invalid data and
change the tooltip to describe the situation, but still allow the user to
navigate away. Then prevent the user from doing anything substantial (like
click a Save button) until the data is valid.

Bonus suggestion: Don't use a textbox. Maybe a spinner or slider control
won't exhibit the same quirkiness.

Good luck,
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com