PDA

View Full Version : Date Format Issue from Input Box



paddysheeran
02-19-2009, 10:00 AM
Hi all,
I'm using the following code to get the user to enter the Week commencing date of a report into cell K12 of the Performance_Report sheet. The macro works fine apart from returning the year as 1900. I've searched all over the net and cant find a reason why this is happening - can anyone help?

Sub Date_Entry()
Dim Request As String
Dim ReportTitle As String
Dim DefValue As Date
Dim ReportDate As Date

Request = "Please enter the Week Commencing Date"
ReportTitle = "CEC Weekly Performance Report"
DefValue = Date - 7
ReportDate = Val(InputBox(Request, ReportTitle, DefValue))
Sheets("Performance_Report").Select
Range("K12").Value = ReportDate
Selection.NumberFormat = "dd mmmm yy"
End Sub

CreganTur
02-19-2009, 10:38 AM
Welcome to the forum- it's always good to see new members!

When you post code, please wrap it in VBA tags- it will format it according to VBIDE, which makes it easier to read.

The return from an input box is of the string data type. I noticed that you tried to use the Val function to get the date, but the Formt function would be a better fit for you because it actually formats your date. Val is a bad fit for what you're trying to do, as it is designed to only pull the numbers out of a text string. I also set ReportDate to a string format- left as Date format it would overwrite the special formatting that is being applied.
I also removed the selection from your code- selection can cause code slowdowns because it causes changes and events in the UI.

Sub DateTest()
Dim Request As String
Dim ReportTitle As String
Dim DefValue As Date
Dim ReportDate As String

Request = "Please enter the Week Commencing Date"
ReportTitle = "CEC Weekly Performance Report"
DefValue = Date - 7
ReportDate = Format(CDate(InputBox(Request, ReportTitle, DefValue)), "dd mmmm yy")
ActiveWorkbook.Sheets(1).Range("K12").Value = ReportDate
End Sub


HTH:thumb

Bob Phillips
02-19-2009, 10:40 AM
Sub Date_Entry()
Dim Request As String
Dim ReportTitle As String
Dim DefValue As Date
Dim ReportDate As Date

Request = "Please enter the Week Commencing Date"
ReportTitle = "CEC Weekly Performance Report"
DefValue = Date - 7
ReportDate = CDate(InputBox(Request, ReportTitle, DefValue))
With Sheets("Performance_Report").Range("K12")

.Value = ReportDate
.NumberFormat = "dd mmmm yy"
End With
End Sub

Kenneth Hobs
02-19-2009, 10:46 AM
The user can put anything in that so it is no wonder you get odd results.

I would recommend a date control method. You can use this as an example:
http://vbaexpress.com/forum/showthread.php?p=176132

CreganTur
02-19-2009, 10:51 AM
The user can put anything in that so it is no wonder you get odd results.

I would recommend a date control method. You can use this as an example:
http://vbaexpress.com/forum/showthread.php?p=176132

That is a much better method, but if there is some reason that you cannot use it, then you should add some validation code to your routine- especially the IsDate function. Take a look at it in Excel Help for all the details.