Ivyleaf
12/18/2006 11:17:00 PM
On Dec 19, 8:20 am, timmulla <timmu...@discussions.microsoft.com>
wrote:
> Can anyone help me with a billing calculator that I'm creating. I need help
> with Textbox2 and Textbox3.
>
> UserForm1.Textbox1.Text = manual input for a quarterly fee $.
>
> UserForm1.Textbox2.Text = The value of this textbox needs to be the number
> of days in a quarter. I want a user selection on Calendarcontrol1 to
> determine the appropriate number of days in a quarter. I believe that the
> number of days in a quarter can change from year to year (i.e. Feb 28 days or
> 29 days), so I'm hoping that the code can adjust for this.
>
> My quarters are the following:
> 1. Jan 1 - Mar 31
> 2. Apr 1 - June 30
> 3. Jul 1 - Sep 30
> 4. Oct 1 - Dec 31
>
> If a user selects Oct 20 in CalendarControl1 I want the value of Textbox2 to
> be the number of days b/w Oct 1 and Dec 31. Can somebody help me accomplish
> this?
>
> UserForm1.Textbox3.Text = Format(Val(UserForm1.Textbox2.Text - (last day of
> the quarter, which would be determined by the calendarcontrol1 selection -
> CalendarControl1.value)
>
> UserForm1.Textbox4.Text = Format(Val(UserForm1.TextBox1.Text) / Format
> (Val(Userform1.Textbox2.Text),"00.00")
>
> UserForm1.Textbox5.Text = Format(Val(Userform1.TextBox3.Text) *
> Val(Userform1.Textbox4.Text),"00.00")
>
> Any help would be appreciated.
>
> --
> Regards,
>
> timmulla
Hi Timmulla,
I am not quite sure what the relevance of textbox3 - 5 had to your
question, but the following will calculate the number of days in the
quarter for you:
Private Sub CalendarControl1_Click()
Dim SelDate As Date
Dim SelYear As Integer
Dim StartMth As Integer
Dim CurQtr As Integer
Dim DaysinQtr As Integer
Dim StartDate As Date
Dim EndDate As Date
SelDate = CalendarControl1.Value 'Get selected date
from calendar
SelYear = DatePart("yyyy", SelDate) 'Extract year from
date
CurQtr = DatePart("q", SelDate) 'Determine quarter
of selected date
Select Case CurQtr 'Set start month
depending on quarter
Case 1
StartMth = 1
Case 2
StartMth = 4
Case 3
StartMth = 7
Case 4
StartMth = 10
End Select
StartDate = DateSerial(SelYear, StartMth, 1) 'Assemble start
date
EndDate = DateAdd("m", 3, StartDate) - 1 'Calculate end date
DaysinQtr = DateDiff("d", StartDate, EndDate) 'Evaluate
difference between dates
TextBox2.Text = DaysinQtr
End Sub
This could be abbreviated considerably, but I extended it for the
purpose of explaining the process.
Cheers,
Ivan.