Help with Focus/Lost Focus

[Kid_Coke]

Here is the code another member gave me.


Private Sub TextBox1_Change()
If Val(TextBox1.Text) < 1 Or Val(TextBox1.Text) > 5 Then
MsgBox "Please enter a number between 1 and 5".
TextBox1.SetFocus
End If
End Sub

That works fine, it limits users from entering values less than one and greater than 5 but if the user simply presses enter or tab the box remains blank.

I have tried several ways to capture this event. Is this a problem with the AutoTab feature?

Thanks.

[TheBionicOrange]

Try using the LostFocus event :

Private Sub Text1_LostFocus()
    If Val(Text1) < 1 Or Val(Text1) > 5 Then
        MsgBox "Valid Values between 1 and 5 !"
        Text1.SetFocus
    End If
    
End Sub

[Kid_Coke]

There is no lostfocus event in excel vba.

Is there another way to do this?

[TheBionicOrange]

ummm .... I hate to disappoint you but Im afraid there is.

I am presuming you are using a textbox, based on your code above.

[Kid_Coke]

I tried to write my own

Private Sub Textbox1_lostfoucs()

and it did not work ... any reasons?

[TheBionicOrange]

Ummm ..... you've spelt LostFocus as "lostfoucs"

Just as a matter of interest ..... if you double click your textbox whilst in design mode this takes you to the default "Change" event for a textbox.

If you use the pull-down box in the VBA environment that lists all the events for the textbox can you not see "LostFocus" listed ?

[Kid_Coke]

Ok ...

I wrote the following code just to test

Private Sub TextBox1_LostFocus()

If TextBox1.Text = " " Then TextBox1.SetFocus
End If


End Sub


It never fires. I appologize, once I corrected the spelling the IDE picked up the even, but it never fires.

[Kid_Coke]

I can't post a pic, but I don't have the LostFocus event in my drop down.

However, when I typed it in it recognized it.

[TheBionicOrange]

Private Sub TextBox1_LostFocus()

If TextBox1.Text = " " Then TextBox1.SetFocus
End If


End Sub

Rather than that, try this :

Private Sub TextBox1_LostFocus()
    If trim(TextBox1.Text) = "" Then TextBox1.SetFocus
End If

End Sub

Bear in mind its not ideal to code in this way. What I mean is there may be a scenario where the user may want to "pass through" the textbox, and your code will always force them back in.
If you are just trying to trap a valid value then only do the above if the user enters a value in, such as :

Private Sub TextBox1_LostFocus()
    If trim(TextBox1.Text) <> "" Then 
        If Val(Textbox1.text) < 1 or Val(Textbox1.Text) > 5 then 
             msgbox "Invalid Value !"
             Textbox1.Text = ""
             Textbox1.Setfocus
        End If
    End If   

End Sub

You may also want to put code in the "Keypress" event of the textbox that only allows numeric values, such as :

Private Sub TextBox1_KeyPress(KeyAscii As Integer)
    Select Case KeyAscii
        Case 8, 13, 48 To 57
            ' OK
        Case Else
            KeyAscii = 0
    End Select
    
End Sub

Hope that helps ....

[Kid_Coke]

Mr. Orange,

Your input is greatly apprecaited.

However, I copied the code you provided and pasted it into a test project that has two textboxes with the autotab property set to true and the max length as 1.

The event is never fired?!?!?!?




I have tried the keypress capture events you mentioned earlier, but it doesn't seem to work. For example if I use the Keypress and capture the ENTER key I can get a msgbox to pop up and warn the user that the key is not good, but the focus goes to the next text box.

Bascially I have 20 textboxes, I don't want users to enter values other than 0,1,2,3,4,5, or 9. I don't want blanks either.

Maybe you can offer an alternative method.

Thanks.

[RobDog888]

I remember reading somewhere that you can not trap the tab keypress in the keypress event.
Try using the KeyDown event instead.

[Kid_Coke]

Attached is a sample xls file.

Notice that textbox1_changed checks for valid values.

If the user types the number 8 for example the msgbox tells the user that the value is no good and puts the textbox back in focus but if the user presses enter the value of 8 remains.

I want to avoid this from happening

[RobDog888]

Your zip file it empty.

I think I know what you mean so, if you place some code in the KeyPress event like this it should do it.

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
        If TextBox1.Text = 8 Then MsgBox "Invalid Value"
    End If
End Sub

Also, you can check out my CodeBank example of creating Excel Events.

HTH

[Kid_Coke]

Try this one???

[Kid_Coke]

So I tried the KeyDown Event.

here is what happens. If I step into each line of the code

If KeyCode = vbKeyReturn Then
TextBox1.setfocus
end if

it works but as soon as it hits the end if statement the focus is given to Textbox2. Is that from the autotab? Is there a way around this?

Thanks.

[Kid_Coke]

This code seems to work to capture the Enter and Tab keys.

If KeyCode = vbKeyReturn Then
KeyCode = vbKeyShift + vbkeyctrl

End If

If KeyCode = vbKeyTab Then
KeyCode = vbKeyShift + vbkeyctrl
End If




NOW the only way a user can get around my program is to click directly into the next textbox

How can I prevent users from clicking on my textboxes?

[RobDog888]

Set the .Enabled property to False for all of your textboxes except the first one. Then
as they successfully complete the first, when you allow then to go to the next one, enable that one
and so on.

[Kid_Coke]

Spoke to soon.

Now even when users enter valid values the autotab feature doesn't kick in.

[RobDog888]

Oh, thats because you forgot to add the code to evaluate the contents of the textbox.

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Or KeyCode = vbKeyTab Then
        If TextBox1.Text = 8 Then MsgBox "Invalid Value"
    End If
End Sub

[Kid_Coke]

Gentlemen ... and err ... women ???


I finally have all of the behaviors I desired on my textboxes.

EXCEPT the damn mouse. I can now prevent users from going forward until a vaild value is entered. However, a user can simple click outside of the textbox to get around my little trap.

is there a way to disable the mouse on my form or on my text boxes????


Tic. ... Tic ... Tic .... {BOOM!}

[RobDog888]

Set the .Enabled property to False for all of your textboxes except the first one. Then
as they successfully complete the first, when you allow then to go to the next one, enable that one
and so on.

Did you see my other post?

[Kid_Coke]

Robb I appreciate your input but I think I got in another way

I used the keydown event to catch the enter and tab, and the _change event to validate the values.

I liked your way but I realized that I could not allow the user to move backwards without way more code. ..

Is there a mouse_click event in excel vba????

if there is, its not on my list of events ... could someone tell me the arguments that sub needs?


Thanks.

[RobDog888]

The GotFocus event will work for you under either scenerio (keypress or mouseclick.

Private Sub TextBox1_GotFocus()

[Kid_Coke]

Here is the new problem ... user clicks on a random cell ... how do I tell my userform what row the user clicked?

[RobDog888]

.ActiveCell

[salvelinus]

I've had problems with SetFocus as well, at least in Access. The order events fire sometimes can screw up what you intended. I had a problem when using SetFocus moving from a textbox to a button and then clicking the button. I'd have to click it a couple times, when it would appear to run once, but actually ran twice. As best as I could figure, the AfterUpdate event of the textbox was getting mixed in with the GotFocus and/or Click event of the button.
I just set it up for taborder instead.

[Kid_Coke]

OK... Somethings up. I don't have a mouseover event.

What should I use instead.

I want a label to change color when the mouse is over it.

That is all.'

[RobDog888]

Use the mousemove event.

Private Sub Label1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
 
End Sub