?
Solved

Forms in Excel - MaskEdBox control problem

Posted on 2003-03-07
6
Medium Priority
?
416 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
[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
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

764 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