Determine Leap Year in User Form 4

[RP1America]

I have a user form with two comboboxes.

The first a user selects a year. (txtYoA)

The second is a month/last day of month combination (e.g. 1/31) containing all 12 months. (txtPYE)

If the user selects a year that is a leap year in the first combobox, I want the second combobox to additem 2/29 instead of 2/28.

Using:

IsLeapYear = Month(DateSerial(j, 2, 29)) = 2

and

If IsLeapYear = False Then
    .AddItem "2/28"
    ElseIf IsLeapYear = True Then
    .AddItem "2/29"
    End If

How do I insert the selected value of txtYoA on the fly? The code above currently returns true (assuming because it is basing off of this year - 2012?). So if the user selects 2011 from the txtYoA combobox, how do I incorporate that into the code?

 

[SkipVought]

hi

Function IsLeapYear(dte as date) as boolean
   if dateserial(year(dte), 3, 0) = 29 then
      IsLeapYear = TRUE
   else
      IsLeapYear = FALSE
   end if
end function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

Compile Error: Argument not optional

I don't think I'm following.

Function IsLeapYear(dte As Date) As Boolean
   If DateSerial(Year(dte), 3, 0) = 29 Then
      IsLeapYear = True
   Else
      IsLeapYear = False
   End If
End Function



Private Sub userform_Initialize()

Dim i As Long
For i = 0 To -3 Step -1
Me.cboYoA.AddItem Format(DatePart("yyyy", DateAdd("yyyy", i, Date)), "00")
Next i

With cboPYE
    .AddItem "1/31"
    If IsLeapYear = False Then
    .AddItem "2/28"
    ElseIf IsLeapYear = True Then
    .AddItem "2/29"
    End If
    .AddItem "3/31"
    .AddItem "4/30"
    .AddItem "5/31"
    .AddItem "6/30"
    .AddItem "7/31"
    .AddItem "8/31"
    .AddItem "9/30"
    .AddItem "10/31"
    .AddItem "11/30"
    .AddItem "12/31"
End With

End Sub

 

[SkipVought]

your use here requires that YOU supply a DATE argument to the function.

But you don't even need that function for what you are doing

        .AddItem Format(DateSerial(Year([b]YourDate[/b]),3,0),"m/d")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

in fact you could do this

dim iMO as integer
With cboPYE
  for iMO = 1 to 12
    .AddItem Format(DateSerial(Year(YourDate),iMO+1,0),"m/d")
  next
End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

No year is selected at the time you load the values of cboPYE !

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[RP1America]

No matter what year is selected in cboYoA, cboPYE is still listing out a 2/28. If I select 2012 in cboYoA, cboPYE needs to list 2/29 as an option.

Private Sub userform_Initialize()

cboYoA.Value = Format(DatePart("yyyy", Date), "00")

Dim i As Long
For i = 0 To -3 Step -1
Me.cboYoA.AddItem Format(DatePart("yyyy", DateAdd("yyyy", i, Date)), "00")
Next i

Dim iMO As Integer
With cboPYE
    For iMO = 1 To 12
        .AddItem Format(DateSerial(Year(cboYoA), iMO + 1, 0), "m/d")
    Next
End With

End Sub

 

[PHV]

Year(cboYoA)
????
cboYoA is supposed to be an year, not a date ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

        .AddItem Format(DateSerial(Year(cboYoA), iMO + 1, 0), "m/d")

cboYoA MUST BE A DATE!!!

Or you must replace [highlight]Year(cboYoA)[/highlight] with a YEAR value like 2012. This year, 2012, returns...
[tt]
1/31
2/29
3/31
4/30
5/31
6/30
7/31
8/31
9/30
10/31
11/30
12/31
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

My apologies. I now see the error and thanks to your help now have it working properly on the initialize sub. However, I am thinking that I will need it in a cboYoA_Change sub as well so that when cboYoA is changed from 2012 to 2011, cboPYE will list out a 2/28.

Is there a way to remove items from a combobox and readd them?

 

[SkipVought]

The code ALWAYS returns the CORRECT last day of February, for ANY given year.

No need to remove ANYTHING, except at the beginning of a new year, then just CLEAR the control and reload all months!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

How about changing Style of both combo boxes to 2 - fmStyleDropDownList and just this code (10 years prior and 10 years after this year):

Option Explicit
Dim i As Integer

Private Sub UserForm_Initialize()

For i = Year(Date) - 10 To Year(Date) + 10
    cboYoA.AddItem i
Next i

cboYoA.Text = Year(Date)

End Sub

Private Sub cboYoA_Click()

With cboPYE
    .Clear
    For i = 1 To 12
        .AddItem Format(DateSerial(cboYoA.Text, i + 1, 0), "m/d")
    Next
End With

End Sub

Have fun.

---- Andy