Free Microsoft Excel 2013 Quick Reference

VBA Input Box Date Validation

I am attempting to validate a date from an input box with the following code (which does not work and no doubt reveals my lack of vba experience)

dweekend = InputBox("Please enter week ending date (mm/dd/yy)", "WeekEnding")

If dweekend.Value < TODAY() - 30 Then GoTo error1
If dweekend.Value > TODAY() Then GoTo error2
End If

error1: MsgBox ("Date is older than 30 days")
error2: MsgBox ("Date is not valid")
The variable "dweekend" is set as a date. What I am trying to accomplish is for the user to have another opportunity to enter the date if what was entered is older than 30 days or greater than today but I don't know how to get the user back to the input box if this occurs. Otherwise the date would be used and the procedure would continue.

Excel 2003 / Windows XP
Thanks for your time

Post your answer or comment

comments powered by Disqus
need to code that will validate that the user has input the date (via InputBox) in the correct format. The user will be prompted to input the date in the format: Month (first three letters only) [space] Year (4-digit year), e.g., Jan 2005.

I am having trouble validating the month format. Any suggestions would be greatly appreciated. I am sure this is basic VBA but I am feeling VBA-challenged today!

Does anyone have a VBA code that will change a field on a pivot table using
an input box?

Ex: The Row field of the pivot table is currently 'Date'. I want an input
box stating, 'Please enter the name of the field you want to change the Row
field to.' I would enter 'Week'. The Row field would change from 'Date' to

Does anyone have or know where I can get this code? I've look throughout the
internet and was unsuccessful in finding one.

Thanks in advance.

I am looking for user input-box,
I want to create two buttons link to macros, when I click on 'start date' botton,
I need a popup box with message, 'enter start date', when I enter
the start date, I want to put that date in cell 'a10', and same as
end date, I want to put 'end diate' in 'a11'.

Can somebody help me with this vba excel tools, or any links
to the same please.

Thank you in advance.

Hi All,
Thanks for all the help & tips I've got from this forum so far.

I'm trying to develop this code to ask the user to input the Start Date & End Date of a report (I've got 2 Input Boxes).

With the inputed responses, I would like to run an Auto Filter with these dates as the criterion.

Here's what I've got so far, but it doesn't work.....It's not filtering anything apart from the first record, and that's probably only because it's there !!!!!
I've used dates that I know there are records for but they don't get filtered.

To build this macro I started with the Input Boxes, got them going & then started a seperate macro to do the Auto Filter (with selecting dates from the lists in the criteria box), then let it do that filter, then copied the main part of that macro onto the end of the first macro & changed the original dates to the Input Box names.
Maybe that's where I'm going wrong ?????

Column F is formatted as Date, dd-mm-yyyy

      StartDate = InputBox("Please enter START date of Report - DD-MM-YYYY")

    EndDate = InputBox("Please enter END date of Report - DD-MM-YYYY")

    Selection.AutoFilter Field:=1, Criteria1:=">=StartDate", Operator:=xlAnd _
        , Criteria2:="<=EndDate"
    Range("BC2").Select (I'd like this to be the Start Date)
    Range("BC3").Select (End Date)
End Sub
I haven't got any validation checks in the macro as you can see, because I'm not sure of the syntax of Date ones so if anyone can help me with that as well, that would be much appreciated. FWI, the dates can be back to Jan-2008 and as new as last week.

On that part I would like to paste the dates from the Input Boxes to the 2 cells at the end of my macro.
I have looked on here to see how to paste Input Box responses but couldn't find anything as simple.

As the next part of this process, I'll be copying the filtered records to Sheet 3 so again, any help with that would be appreciated.
I should be able to at least start the macro, but where I would need help is telling the macro to stop when it comes to the last filtered record.

Thanks again for all the help.


Hi all,

My latest dilemma is this:

I need my user to type in the start and end date of a fiscal period (not always the same length, so need both dates). Obviously I can have two separate input boxes, but that is a little inelegant. I'd prefer a single input box that requests and accepts two pieces of data.

Is this possible? Can you point me in the right direction?


Sorry to be a pain this morning, but I have one more question for any VBA experts out there....for I know next to nothing about it.

I need some code that will prompt the user with an input box asking for a number. The catch is, each number possibility (1:28) coincides with a column (B:AC).

1=B, 2=C, 3=D, 4=E, etc...

So, if the user inputs the number 12, I would like it to see that number 12 as column M, and cut a selection from M2:AC14 to be pasted at M3:AC15....essentially moving the entire selection from the user-selected column to the end down one row.

I hope that makes sense...

This was my previous post:
- I'm using an input box to prompt users to enter a column. How can I determine if what they entered is a valid column?

Will did a great job answering my previous post, now I have another question.

I can check (thanks to Will!) that the user enters a column valid for the spreadsheet; however, I'm stumped on how to trap if the user enters a totally invalid value for the column prompt. e.g. what if they enter xxx? I currently get "Run-time error '13': Type mismatch".

I've read a little about On Error, but I'm not sure how to code it.


I wish to have two input boxes come up when the workbook is opened and for the first one to ask for the delivery date, putting the entered data in “J1” and then the second one to ask for the delivery day and for that data to go into “J2”.

I have been trying to figure out to how to do this but with no luck.

Sorry I am still learning VBA…. And help would be greatly appreciated.

Hi again folks,

Busy again with more data manipulation projects :D

First off, here is a sample of the data I am working with:

1. OFFERED,External,30/06/04 09:00,64,Wednesday
1. OFFERED,External,30/06/04 09:30,90,Wednesday
1. OFFERED,External,30/06/04 10:30,105,Wednesday
1. OFFERED,External,30/06/04 11:30,91,Wednesday
1. OFFERED,External,30/06/04 12:00,98,Wednesday
1. OFFERED,External,30/06/04 12:30,85,Wednesday

The data source is historic and has new data appeneded onto the end. THis means that the volume of data is very high.

What I would like to do, is have VBA to allow me to input two dates; this will will specify a range of dates. Then I would like to remove all the rows of data that do not fall within that particular range.

Any and all help is greatly appreciated.

Many thanks in advance,

Hello All,

I have tried to search for this but I keep getting an error about my search being too long.

I am trying to create a VBA code that will generate an Input box if a cell changes from 0 by way of a formula calculation.

The input box would then create a value which will be put into a cell on a different worksheet.

Finally if the value on the worksheet is below a certain amount then I need a message box to appear.

The code I currently have for the first bit almost works except I get stuck in the Input box loop and can't get out.
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Dim lel As Integer
    If Sheets("Maternity Details").Range("L19") <> 0 Then
    lel = InputBox("Enter the lel", 0)
    Sheets("Set Period").Range("A23") = lel
    End If
    End Sub
Any help would be greatly appreciated.

Thank You


I entered the following code:
Dim new_data As Single

I set the variable value with the code:
new_data = inputBox(prompt, title)

Everything works except when I click the cancel button in the input box, or I don't enter any value in the input box and click OK button, I get an error message.
"Run-time error '13':
Type mismatch".

I think it has to do with Excel returning a string value in this case and that doesn't agree with the "Single" data type that I set.

Does anyone know a solution?

how can I change change the height and width of an input box manually?



I am attempting to lock down some of the VBA functionality to a limited user group. I want a VBA input box to display *** (stars) rather than the typed characters.

How do I acheive this?

Regards DavidB

Oh Great Guru's Help


car = InputBox("Please enter Car number", "Number")
d_name = "Smith"
Name = InputBox("Please enter Driver Name for car #" & car, "Driver Name", d_name)

I would like to change the d_name to a lookup on the sheet.

For example if the user enter car 11B then Mayes would be the default the name input box.

Car number is in column A and name is in column B

Number Name
5R Reed
20 Ivy
11B Mayes
99 Roepke
92K Keegan
8S Mintz

I have wasted about 4 hours on this with absolutly no sucess.
Please help - I'm lost here.

Thanks to all.


I am trying to use 5 input boxes to paste information in sheet 1 of my excel
worksheets. Which i want to add to a list so i can access the information
inside the list easy because it can be alphabetized. What should i put into
the submit button to add the information to sheet 1 into the list. Also is
there any other code i need to get the close button to work besides 'Unload

If you havent guessed by now i am a newbie at this.

Thank you for your help,


Ok, that's pretty much what I'm trying to achieve but couldn't find anywhere else:

If specific cell is clicked (selected), let's say range(2, 2), then pop up an input box (with "OK" and "cancel" buttons) to edit/enter that cell's value (text).

Thank you


Is there a way to change a line of vba code using an input box?

The current macro downloads a file from our intranet. However the filename changes each week. It is only the last portion of the filename that changes since this part is the date it is created. e.g filename010507.xls filename090507.xls

What I want to do is for an input box to pop up, the user then enter the date and this will then replace the existing filename in the code with the new date.

Maybe I am looking at this in a complicated way but cant think of another way to do it.

Any help is appreciated.

Many Thanks


Hi there,

I very new to all this VBA stuff and have found the info and the links in this forum fantastic. I have managed to set up a database using a userform by searching through all the good stuff here. However, I am having a problem with validating a date entry.

Having read all the info on US vs UK date entry, I’ve managed to get that right by using;

ActiveCell.Value = (DateValue(txtdate.Text))
'txtdate is the text box used to enter date via user form.

The problem comes if the user does not enter a date when clicking OK to input the data into the spreadsheet. When this happens I get a error message; Runtime error "13" Type mismatch (assumingly because the lack of data is not what is expected).

How can I run a check when the user inputs the date to ensure that it is in the correct format and prevent the error (I have tried a few of the solutions within this forum but can’t seem to get them to work when the above code is used).

Many thanks and apologies if this has not come out in the correct format (it's my first time!!).


I have an input box that pops up to enter values in a cell. I need the box's default value to be the current date. I found a thread that had a VBA that was close, but no banana. Here is what I have now that just opens a blank box where I have to add the date manually:

returnvalue = InputBox("Date Requested", "Date Requested") 
Sheets("this Week").Range( 'F7") = returnvalue

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks for the assistance!

I have designed a spreadsheet to calculate time and speed averages of a ships voyage.
When using a date and time value entered into a input box the value in the cell is reversed form the U.K date format to the U.S date format. this has been causing great amount of fustration to me. the cell has the correct date format i want and the excel system is configured to the U.K format.

If anyone can help heres my program to allow you to look at it my knowlege is not very good of VBA.

When entering the date in the FAOP from the drop down menu as dd/mm/yy hh:mm in is reversed.


I am trying to ensure that the information entered in an input box is only accepted when it is a letter first then 4 digits. I have help with this using data validation within excel but it doesnt work when I run the VBA routine, any help gratefully received.


For the life of me, I cannot get this to work; either it keeps looping,
doesn't return the right message, or bombs out. All I'm trying to do is ask
the user to input a date in mm/dd/yy format.
1) If they hit cancel or don't enter anything, then they should get a
message saying they must enter a response.
2) If they enter something but it is not in the mm/dd/yy format, they should
get a message that it is not in the correct format.
3) If they enter it correctly, their response should be put into cell A1.

Can someone PLEASE help me? What I have is:
Sub GetDate()
Dim Response As String
Dim Msg As String
Msg = "Enter the contract start date, in mm/dd/yy format; then click on 'OK'."
Response = InputBox(prompt:=Msg)
If Response = "" Then
MsgBox ("You must enter a response; please try again.")
End If
If Response <> Format(CDate(Response), "mm/dd/yy") Then
MsgBox ("Enter a valid date in mm/dd/yy format; please try
End If
Range("A1").Value = Response
End Sub

I have written VBA code to enter the beggining date of the financial year end, in this eg 01/10/2007 (in format dd/mm/yyyy). When the input box appears after activating the macro, I enter the date as 01/10/2007, but it appears in cell M1 as 10/01/2007.

How do I write code to convert this in format dd/mm/yyyy i.e to apear in my example as 01/10/2007.

See my code below
Sub Enter_Dates()
Dim Mydate As String
ActiveCell.FormulaR1C1 = InputBox("Enter the opening financial date of your Financial Year End for eg 01/10/2007")
End Sub
Your assisistance will me most appreciated



I am still somewhat new to VBA for excel, and have a question regarding a function I would like to implement in a macro. I have the code for the input box:


Public Sub DateInput()

Dim InDate As Date

InDate = InputBox("Please enter a date in the format DD/MM/YYYY:")

What I would like it to do is use the input date to check a certain "date" column of a spreadsheet and delete all rows that contain a date less than the input date InDate. Any tips on how to go about doing this?

Also, is there a way to validate/create an error message if a date format is not inputted by the user... instead of what it currently does, breaking the code?

I really do appreciate any help I can get with this, even just some tips. Thank you and let me know if you need more clarification!

No luck finding an answer? You could always try Google.