Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with code
On Dec 19, 8:20 am, timmulla
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
How to assign same code inside Option button code space ?? | Excel Programming | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming |