Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1160
  • Last Modified:

Excel 2000 VBA - Setting the focus on a text box

Hi.

I have created a userform in Excel with several text boxes.  In essence, I am trying to validate a user's input.  

In this specific example, the user should enter a 10 digit phone number as ten digits (i.e. no dashes, etc.) If the user does not enter the data correctly, I would like the user to receive a message and then for the focus to be returned to the textbox.

All is working well EXCEPT the focus does not return to the textbox.  I am using the before update event.  I have also experimenting with placing the code in the after update event and the exit event but no luck.  Any help would be appreciated.  The code I am using is as follows:

Private Sub txtMIN_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If txtMIN.TextLength <> 10 Then
MsgBox ("Please enter a 10 digit MIN without dashes or other characters.")
Cancel = True
frmQA2K3.txtMIN.SetFocus
End If
End Sub

I will also add additional validation to ensure the data is all numberic after the above is working.
0
LuckyLee
Asked:
LuckyLee
  • 4
  • 4
  • 2
  • +1
1 Solution
 
Steve KnightIT ConsultancyCommented:
That works as-is for me whether I click out of the field using mouse or tab out using keyboard.... have you traced it through (put a break point (F9) on the IF line and check it is executing this particular bit of code?


Steve
0
 
sebastienmCommented:
Are you sure your textbox name is txtMin: I just tried your code and had first forgotten to set the text box name... so it wasn't working :-)
But after renaming the textbox to txtMin , it worked just fine, as dragon-it said.

Sebastien
0
 
LuckyLeeAuthor Commented:
Thank you both for your comments.

The IF statement is being executed.  I am consistently receiving the msgbox text.

I am also positive of the textbox name.  The subroutine was created through the vb editor's object dropdown menu so vb provided the name ensuring it is correct.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LuckyLeeAuthor Commented:
In case anyone is interested, I was able to resolve this issue.  The userform's modal property was set to False, which is actually where I would like the property to be set.

When I set the property to True, the code works perfectly.  I will gladly award the points if someone can share with me how to get the code to work with the modal property set to False.
0
 
Steve KnightIT ConsultancyCommented:
Don't even have the ShowModal option on my Excel 97.... had a quick go on XL2000 and it seems as soon as you open the MsgBox the focus is lost for good.  Without the MsgBox all works nicely.... I suppose you could add a text box to the form to show the message instead ?

Let us know if you find a fix!

Steve
0
 
PhetuCommented:
Hi,

The only workaround that i've found is to use the SendKey in your IF statement to getback to the previous field like:

SendKeys ("+{tab}")

It's not the cleaner way that i ever saw but...it work!

Phetu
0
 
PhetuCommented:
If you want to try the sendkey, get rid of the Cancel=True because it cause looping.
0
 
LuckyLeeAuthor Commented:
Thanks Phetu.  

I like your approach but ultimately, it does not work.  If I remove the Cancel=True statement, a user can bypass the data validation.

A user can enter in the wrong data, get the data validation, the Sendkeys would send them back to the text box and they can then hit Tab to go to the next box without changing the data.
0
 
PhetuCommented:
Hi,

You're rigth. What would you says by moving your code to the field Exit routine(Private Sub txtMIN_Exit). And this is working great.

Phetu
0
 
PhetuCommented:
Thanks

Phetu
0
 
LuckyLeeAuthor Commented:
Thank you Phetu!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now