Solved

Excel: only allow user to enter a date

Posted on 2010-09-22
5
889 Views
Last Modified: 2012-05-10
What VBA would I need so that a user can only enter a date in my textbox in my Excel spreadsheet. (i.e. if the user enters "Hello", the textbox won't allow it, maybe it will delete "Hello" and display a messagebox saying: "Dates only please" etc.)
0
Comment
Question by:ouestque
[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
5 Comments
 

Accepted Solution

by:
priteshjchauhan earned 167 total points
ID: 33740270
   With Selection.Validation
        .Delete
        .Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="1/1/1900", Formula2:="=NOW("
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = "Error"
        .InputMessage = ""
        .ErrorMessage = "Please enter a valid date."
        .ShowInput = True
        .ShowError = True
    End With
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 33740430
Hello ouestque,

do you mean a textbox or a cell? A date is a number, so data validation code for a cell only needs to check if the number is greater than 0, like the code below. If you want to limit data entry of future dates, then the previous code will work, if you add a closing parens in the NOW() parameter.

You can also specify that the maximum date is, for example 30 days from today. For this, use

Formula2:="=NOW()+30"

If you want to validate a textbox, the code will need to be completely different. Please clarify.

cheers, teylyn
With Selection.Validation
        .Delete
        .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
        Operator:=xlGreater, Formula1:="1"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = "Wrong data type"
        .InputMessage = ""
        .ErrorMessage = "Enter valid dates only"
        .ShowInput = True
        .ShowError = True
    End With

Open in new window

0
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst
Ingeborg Hawighorst earned 166 total points
ID: 33740480
This code will check a textbox and allow only dates.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ActiveSheet.Shapes.Range(Array("TextBox 1")).Select
    On Error GoTo Error01
    vvar = DateValue(Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text)
    Exit Sub
Error01:
    Application.EnableEvents = False
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = ""
    Application.EnableEvents = True
    MsgBox "Please enter a valid date"
End Sub

Open in new window

0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 167 total points
ID: 33741863
Assuming a userform, then something like this (note though that IsDate is quite expansive in its determination of dates):



Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Len(TextBox1.Text) > 0 Then
        If Not IsDate(TextBox1.Text) Then
            MsgBox "must enter a date"
            Cancel = True
        End If
    End If
End Sub

Open in new window

0
 

Author Closing Comment

by:ouestque
ID: 33742123
Awesome answers thanks!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

735 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