Message box prompts for a date and the calculates difference in todays date

[robertguy]

Hi can any one help me please ?

What I am trying to achieve is when a macro button is pressed a messag
box prompts the user to input a date in the format DD/MM/YY.

This date must be validated as a date and not text or a number etc wit
the user being prompted to enter again if incorrect.

Next, another message box would display a message base on the formul
:-


=DATEDIF($B$1070,NOW(),"y")&" years, "&DATEDIF($B$1070,NOW(),"ym")&
months, "&DATEDIF($B$1070,NOW(),"md")&" days"

which would calculate and display the difference in dates in the forma
i.e. Todays date – input date :-

e.g. 2 years, 3 months, 6 days


Many thanks


Ro

 

[Frank Kabel]

Hi Rob
using the Age function from Chip Pearson (see
http://www.cpearson.com/excel/datedif.htm)

try the following
-----

Sub get_age()
Dim ret_value
Dim ret_date As Date
Dim ret_str

While Not IsDate(ret_value)
ret_value = InputBox("Enter a value")
Wend
ret_date = CDate(ret_value)
ret_str = Age(ret_date, Now)

MsgBox ret_str
End Sub


Function Age(Date1 As Date, Date2 As Date) As String
Dim Y As Integer
Dim M As Integer
Dim D As Integer
Dim Temp1 As Date
Temp1 = DateSerial(Year(Date2), Month(Date1), Day(Date1))
Y = Year(Date2) - Year(Date1) + (Temp1 > Date2)
M = Month(Date2) - Month(Date1) - (12 * (Temp1 > Date2))
D = Day(Date2) - Day(Date1)
If D < 0 Then
M = M - 1
D = Day(DateSerial(Year(Date2), Month(Date2), 0)) + D
End If
Age = Y & " years " & M & " months " & D & " days"
End Function

 

[robertguy]

Frank

once again many thanks !!

However, there is one slight problem with the code and that is the use
cannot cancel the message box without inputting valid data, as th
close message box 'x' (top left of box) and the 'Cancel' button doe
not work.

Could you advise me how to activate the Cancel button ?

You help would be appreciated


Regards


Ro

 

[Frank Kabel]

Hi
try the following slightly change:

Sub get_age()
Dim ret_value
Dim ret_date As Date
Dim ret_str

While Not IsDate(ret_value)
ret_value = Application.InputBox("Enter a value")
If ret_value = False Then Exit Sub
Wend

ret_date = CDate(ret_value)
ret_str = Age(ret_date, Now)
MsgBox ret_str
End Sub

 

[robertguy]

Frank


the code works Fine !

However, how do you change the Message box header from "INPUT" to
legend of your own choice and how would you center it ?

How also would you center the prompt message text i.e. insert blan
lines - would you use vbCrLf's

On that note how would you center normal message box headers - as
have to use spaces to center justify.



Any help would be appreciated


Thanks


Ro

 

[Frank Kabel]

Hi
you may have a look at the helpfile for all Inputbox parameters
Try

Application.InputBox(prompt:=vblf & vbtab & vbtab & "Enter a date", _
title:="Date entry", default:=format(now,"mm-dd-yyyy"))

 

[robertguy]

Frank,

I've amended the code you as suggested see below, however, when th
message box prompts for the date it displays today’s date and i
appears to accept any day format.

Can you please advise how to clear today’s date from input line and fo
the date to be checked and validated on to accept format dd/mm/yy.

And finally how do I insert tabs to center it the message box title


You help is as always greatly appreciated



Rob



Sub frank()

Dim ret_value
Dim ret_date As Date
Dim ret_str

While Not IsDate(ret_value)
ret_value = Application.InputBox(prompt:=vbLf & vbTab & vbTab & "Ente
a date", _
Title:="Date entry", Default:=Format(Now, "mm-dd-yyyy"))
If ret_value = False Then Exit Sub
Wend

ret_date = CDate(ret_value)
ret_str = Age(ret_date, Now)
'MsgBox "date !!!" & ret_str
MsgBox "Age of Asset :- " & vbCrLf & vbCrLf & ret_str, vbInformation
"Asset Details"
End Su

 

[Frank Kabel]

Hi
for the first one just delete the part
default:=format(now,"mm-dd-yyyy")
from the code (was just a small addition from me to show the possible
parameters of inputbox)

For the second one: Very difficult - impossible: How should a computer
check that
03/04/04
ist allowed
but
04/03/04
is not. So I would suggest to show in the inputbox whcih format should
be used - the rest is on the users side