input box to worksheet calculation...

[mrowe]

Hi all,

Here's my problem,

I have a created a Form for users to enter client details and billing info that feeds to a worksheet.

On the form I have an input box for an estimated billing date (EBD).

What I'm trying to do (with little success) is to create a calculation on the form which works out the number of days to billing (ie EBD - NOW()
Then
On the work sheet drop it in one of 4 cells depending on how many days the calculation is. (i.e between 1 -30,31-60,61-90,>90)...

Am pulling my hair out... so if anyone has a brilliant idea I would love to hear from you.

Thanks
Matt

 

[Dexter]

May I assume that you have tried IF statements?

 

[mrowe]

I've tried IF's to work out the number of days (i.e =IF(AND($K$4>60,$K$4<=90),"X","")
which is fine...
I don't know how to get this to intact with the input box on the form...
So, when the user types in an date, and clicks sumit, the IF goes to work then, calculates the days between NOW() and the date input and then throws it into one of 4 defined cells (see first post)...
ta
M

 

[DonkeyOte]

On 2002-11-05 09:01, mrowe wrote:
Hi all,

Here's my problem,

I have a created a Form for users to enter client details and billing info that feeds to a worksheet.

On the form I have an input box for an estimated billing date (EBD).

What I'm trying to do (with little success) is to create a calculation on the form which works out the number of days to billing (ie EBD - NOW()
Then
On the work sheet drop it in one of 4 cells depending on how many days the calculation is. (i.e between 1 -30,31-60,61-90,>90)...

Am pulling my hair out... so if anyone has a brilliant idea I would love to hear from you.

Thanks
Matt

Matt you could do this calculation as part of the InputBox in the Macro eg

EBD = InputBox("Date")

Range("a3") = Format(EBD, "MM/DD/YY")

AGEDDATA = Range("A1") - Range("A3")
where A1 = Today()


If AGEDDATA <= 30 Then Range("B1") = AGEDDATA
If AGEDDATA > 30 And AGEDDATA <= 60 Then Range("C1") = AGEDDATA
If AGEDDATA > 60 And AGEDDATA <= 90 Then Range("D1") = AGEDDATA
If AGEDDATA > 90 Then Range("E1") = AGEDDATA

End Sub

 

[mrowe]

Thank you.. this looks like it will do the trick.. thanks for your help
Matt

 

[mrowe]

hi... sorry but one more question... I've now been asked to have the form object as a textBox... I've tried things like EBD = textbox9 etc but get data mismatch errors - do I have to dim As something??
Thanks in advance.
Matt

 

[DonkeyOte]

Is the text box linked to a cell? If so reference the cell.