Texttbox_Change How to Update (my first Post)

[nicholasblack]

Hello, I am reasonably new to Excel, however, I have more experience in VBA. The form I have built has a series of Textboxes for user input. The Input values must be numeric and must be within specified ranges.

The major problem is that all works well except if the value is not within the proper range I can not keep the focus on the textbox. the index keeps advancing and the user must then point and click the mouse back to the textbox inorder to adjust the input value.

Desired result: after the entry is made; check isnumeric. Next check the value to see if the value is < or > than required limits. If the value is not within the limits then select the input value so the user MUST adjust.

There is a msgbox to trap the user responce to "Are you Sure about the value". When the user select "Yes" all work well. It is only when the user selects "NO" that I am not able to force the the focus back to the textbox.

Any Help would be very much appreciated

 

[Marcelo Branco]

Hi,

Maybe

TextBox1.SetFocus

M.

 

[nicholasblack]

Thanks for the input, however, textbox.setfocus does not work in this case. I tried setfocus on beforeupdate, afterupdate, on exit and before Enter of the next indexed textbox.

 

[starl]

Let me see if I understand: after the user enters data in a textbox, you run a verification on the data. You give them a chance to fix their data if there is a problem. If they want to make a change, you want the cursor to remain in the textbox. Correct? If so, try the Exit event, set to Cancel to keep the cursor in the textbox. Like this:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
resp = MsgBox("You sure", vbYesNo)
If resp = vbNo Then
    Cancel = True
End If
End Sub

 

[nicholasblack]

starl, Thank you so much for your consideration and thoughts. The logic is straight forward and to the point. I will update you after I re-write the code. I played around so much with the "textbox.TabIndex" I guess I was the one who "lost Focus". By the Way, Chasing the "TabIndex" is somewhat like playing cat and mouse.

Again, thanks for your help.

Nick

 

[nicholasblack]

Starl, I finished the re-write and your suggested code worked wonderfully. For others like me I will mention that I also put "Cancel = False" in the no fault section in order to continue the procedure.

Again, Thank you for your time, consideration and suggestions. I for one am so happy I found this site where people like yourself take personal time to help others.

As my experience and knowledge grow I will do the same. "Pay It Forward"

Nick