Combobox for birthdays

[richard.dolis]

Hi

Good day to all!

i have 3 combobox.. cmbMonth(Month), cmbDay(Day) and cmbYear(Year) and the purpose of this is to set up their birthdays.

Is there any vba code for this?


thanks in advance

[Logit]

Here is one way :

For a UserForm:

Option Explicit
'Clears and Initializes the form when first loaded.
Private Sub UserForm_Initialize()
With ComboBox1
        .Clear
        .AddItem "January"
        .AddItem "February"
        .AddItem "March"
        .AddItem "April"
        .AddItem "May"
        .AddItem "June"
        .AddItem "July"
        .AddItem "August"
        .AddItem "September"
        .AddItem "October"
        .AddItem "November"
        .AddItem "December"
    End With
    With ComboBox2
        .Clear
        .AddItem "1"
        .AddItem "2"
        .AddItem "3"
        .AddItem "4"
        .AddItem "5"
        .AddItem "6"
        .AddItem "7"
        .AddItem "8"
        .AddItem "9"
        .AddItem "10"
        .AddItem "11"
        .AddItem "13"
        .AddItem "14"
        .AddItem "15"
        .AddItem "16"
        .AddItem "17"
        .AddItem "18"
        .AddItem "19"
        .AddItem "20"
        .AddItem "21"
        .AddItem "22"
        .AddItem "23"
        .AddItem "24"
        .AddItem "25"
        .AddItem "26"
        .AddItem "27"
        .AddItem "28"
        .AddItem "29"
        .AddItem "30"
        .AddItem "31"
    End With
    
    With ComboBox3
        .Clear
        .AddItem "2017"
        .AddItem "2018"
        .AddItem "2019"
        .AddItem "2020"
        .AddItem "2021"
        .AddItem "2022"
        .AddItem "2023"
        .AddItem "2024"
        .AddItem "2025"
        .AddItem "2026"
        .AddItem "2027"
        .AddItem "2028"
        .AddItem "2029"
        .AddItem "2030"
        
    End With


End Sub

For a worksheet :

Option Explicit

Private Sub Worksheet_Activate()
With ComboBox1
        .Clear
        .AddItem "January"
        .AddItem "February"
        .AddItem "March"
        .AddItem "April"
        .AddItem "May"
        .AddItem "June"
        .AddItem "July"
        .AddItem "August"
        .AddItem "September"
        .AddItem "October"
        .AddItem "November"
        .AddItem "December"
    End With
    With ComboBox2
        .Clear
        .AddItem "1"
        .AddItem "2"
        .AddItem "3"
        .AddItem "4"
        .AddItem "5"
        .AddItem "6"
        .AddItem "7"
        .AddItem "8"
        .AddItem "9"
        .AddItem "10"
        .AddItem "11"
        .AddItem "13"
        .AddItem "14"
        .AddItem "15"
        .AddItem "16"
        .AddItem "17"
        .AddItem "18"
        .AddItem "19"
        .AddItem "20"
        .AddItem "21"
        .AddItem "22"
        .AddItem "23"
        .AddItem "24"
        .AddItem "25"
        .AddItem "26"
        .AddItem "27"
        .AddItem "28"
        .AddItem "29"
        .AddItem "30"
        .AddItem "31"
    End With
    
    With ComboBox3
        .Clear
        .AddItem "2017"
        .AddItem "2018"
        .AddItem "2019"
        .AddItem "2020"
        .AddItem "2021"
        .AddItem "2022"
        .AddItem "2023"
        .AddItem "2024"
        .AddItem "2025"
        .AddItem "2026"
        .AddItem "2027"
        .AddItem "2028"
        .AddItem "2029"
        .AddItem "2030"
        
    End With
End Sub

[Maudibe]

Richard,

The following code will take into consideration leap years and the days for each month based on the selection using named ranges on sheet 1. The userform is called Userform1, 3 comboboxes (combobox1, 2, and 3), and a command button (CommandButton1).

HTH,
Maud

Private Sub ComboBox1_Change()
ComboBox2.Enabled = True
ComboBox2.RowSource = "Mo"
End Sub

Private Sub ComboBox2_Change()
ComboBox3.Enabled = True
leap = Val(ComboBox1.Value) Mod 4
Select Case ComboBox2.Value
    Case 1, 3, 5, 7, 8, 10, 12

        ComboBox3.RowSource = "Days4"
    Case 4, 6, 9, 11
        ComboBox3.RowSource = "Days3"
    Case 2
        If leap = 0 Then ComboBox3.RowSource = "Days2" Else ComboBox3.RowSource = "Days1"
End Select
End Sub

Private Sub CommandButton1_Click()
If ComboBox1.Value = "" Or ComboBox2.Value = "" Or ComboBox3.Value = "" Then Exit Sub
MsgBox "d/m/yyyy" & Chr(13) & _
ComboBox2.Value & "/" & ComboBox3.Value & "/" & ComboBox1.Value
Unload Me
End Sub

Private Sub UserForm_Initialize()
ComboBox1.RowSource = "Yr"
ComboBox2.Enabled = False
ComboBox2.Enabled = False
End Sub

HTH,
Maud

[richard.dolis]

Hi Maud, It worked but I want the value in the Comboboxes couldn't be changed before I click the OK button.
Is there any way that the value in comboboxes should be on the list? and will not proceed if not?

By the way, thank you for your help.

[jindon]

3 Comboboxes (Named ; ComboBox1, ComboBox2, ComboBox3)
ComboBox1 for Year, ComboBox2 for Month, ComboBox3 for Day

Private Sub UserForm_Initialize()
    With Me.ComboBox1
        .List = Evaluate("row(" & Year(Date) - 100 & ":" & Year(Date) & ")")
        .ListIndex = 60
    End With
    Me.ComboBox2.List = Evaluate("index(text(""2017/""&row(1:12)&""/1"",""mmmm""),)")
End Sub
Private Sub ComboBox1_Click()
    ComboBox2_Click
End Sub
Private Sub ComboBox2_Click()
    Dim endDay As Long
    If (Me.ComboBox1.ListIndex = -1) + (Me.ComboBox2.ListIndex = -1) Then Exit Sub
    endDay = Day(WorksheetFunction.EoMonth(Me.ComboBox1.Value & "/" & Me.ComboBox2.ListIndex + 1 & "/1", 0))
    Me.ComboBox3.List = Evaluate("row(1:" & endDay & ")")
End Sub

[Maudibe]

Richard,

When the form opens, the month and day comboboxes are disabled. When a year is selected, the month becomes enabled. When the month is selected, the day becomes enabled. The purpose is to let each combobox load the appropriate range to account for the leap years. The date can be changed prior to clicking OK.

HTH,
Maud