?
Solved

Forms in Excel - MaskEdBox control problem

Posted on 2003-03-07
6
Medium Priority
?
420 Views
Last Modified: 2012-08-13
Greetings,

I'm working on an user form in Excel 2000's Visual Basic Editor. The form has two fields to enter specific dates, and I want the fields to have a date mask so the data is entered in an specific date format (mm/dd/yy). If the data is not entered in that format, I want the macro to generate an error message.

I tried adding Maskedbox controls to the form for that purpose, but when I do I get the following error:

"The control could not be created because it is not properly licensed."

How do I solve this? If I can't use that type of control, why do I have it as an available choice? Is there another way to achieve the same results?

Thanks for any help!

Ed

PS - Please remember that I'm working with Excel, not Access.

0
Comment
Question by:ed583
  • 4
  • 2
6 Comments
 
LVL 16

Expert Comment

by:sebastienm
ID: 8090316
ed583,

Use the _Exit event of the text box to validate the entry.
The bellow code does: (assume the box to be Textbox1)
- reformat the entry in mm/dd/yy
- if the reformatted entry is not a date, then warn the user amd select the text in the textbox

'---------------------------------------
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

   With TextBox1
     On Error Resume Next
     .Value = Format(.Text, "mm/dd/yy")
     If Not IsDate(.Text) Then
        MsgBox "Texbox has to be a date"
        .SelStart = 0
        .SelLength = Len(.Text)
        Cancel = True
     End If
   End With
End Sub
'---------------------------------------------

Regards,
Sebastien
0
 

Author Comment

by:ed583
ID: 8090841
Sebastien,

Thank you for the quick response.

The code you provided works nicely. What if, however, the form also had a Cancel button which would allow the user to exit the form without entering any data?
As it stands, using your code, the user cannot cancel unless there's a valid date on the text boxes first.
Should I put it then under the _AfterUpdate event? That seems to work, but I would like some confirmation.

Thanks!
0
 

Author Comment

by:ed583
ID: 8090910
Actually, my _AfterUpdate solution doesn't work that well...I just noticed that once the user gets past the error message, they can still exit the textbox leaving the wrong data. Seems I will have to choose the lesser evil, unless there are any other suggestions?
0
Industry Leaders: 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!

 
LVL 16

Accepted Solution

by:
sebastienm earned 300 total points
ID: 8091011
The problem of the _AfterUpdate, i believe, is that :
- say the user puts "dddjsf" in the box
- then he 'leaves' the box
- a message pops-up since it is not a date.
- now the user is back in the texbox
- then he exits again without changing the content
- therefore there is no update, threfore the _AfterUpdate is not fired this time
... not sure, but i believe that's the way it works.

I tried the following:
- when the form is activated, the Tag property is set to empty string
- when the form is being closed (_QueryClose event) the Tag property is set to "C" (standing for Close)
- now, the _Exit sub of the textbox test the Tag property. So if it is "C" , the message doesn't pop-up.

'---------------------------------------
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

  With TextBox1
    On Error Resume Next
    .Value = Format(.Text, "mm/dd/yy")
    If Not IsDate(.Text) And Not Me.Tag = "C" Then
       MsgBox "ENter a date in TExbox1"
       .SelStart = 0
       .SelLength = Len(.Text)
       Cancel = True
    End If
  End With
End Sub

Private Sub UserForm_Activate()
   Me.Tag = ""
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
   Me.Tag = "C"
End Sub
'----------------------------------------------------

Try it with several scenarios.
There's maybe some better way... i don't know.

Regards,
Sebastien
0
 

Author Comment

by:ed583
ID: 8091185
Thanks for the additional info. I'll try it tomorrow (I'm in a bit of a hurry right now) and I'll let you know.

Thanks again!
0
 

Author Comment

by:ed583
ID: 8094332
Well, I tried this out and its more or less the same. I think its, maybe, because the textbox Exit event occurs before the form QueryClose event, so when the tag is checked during the Exit event, its still empty (not "C"). Thats my theory but I could be wrong.
Still, your ideas are making this work better than what I had before, and since no other suggestions seem forthcoming, you get the points.

Thanks!
0

Featured Post

Industry Leaders: 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 is an article on how to answer questions, earn points and become an expert.
If Skype for Business came with your office 2016 or office 365 installation, you may find that it's almost impossible to either disable or remove it. The application will often launch with each start of Windows, even when explicitly configured not t…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

621 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