?
Solved

Excel 2000 VBA - Setting the focus on a text box

Posted on 2003-02-24
11
Medium Priority
?
1,157 Views
Last Modified: 2008-01-09
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
Comment
Question by:LuckyLee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 43

Expert Comment

by:Steve Knight
ID: 8012165
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
 
LVL 16

Expert Comment

by:sebastienm
ID: 8012561
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
 

Author Comment

by:LuckyLee
ID: 8012587
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:LuckyLee
ID: 8013158
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
 
LVL 43

Expert Comment

by:Steve Knight
ID: 8016537
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
 
LVL 2

Expert Comment

by:Phetu
ID: 8024350
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
 
LVL 2

Expert Comment

by:Phetu
ID: 8024380
If you want to try the sendkey, get rid of the Cancel=True because it cause looping.
0
 

Author Comment

by:LuckyLee
ID: 8027023
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
 
LVL 2

Accepted Solution

by:
Phetu earned 300 total points
ID: 8031119
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
 
LVL 2

Expert Comment

by:Phetu
ID: 8037269
Thanks

Phetu
0
 

Author Comment

by:LuckyLee
ID: 8037566
Thank you Phetu!
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
New style of hardware planning for Microsoft Exchange server.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question