# Thread: Fill cells with day of month... (2000)

1. ## Fill cells with day of month... (2000)

Have a sheet with 2 combobox MONTH and YEAR.
Month filled with: Gennaio, Febbraio...Dicembre
Year filled with:2008,2009...2020
Now based selection of month and year from the 2 combobox, how to fill the cells (started from B4 to AF) with the day in this format 01,02,03...31.
tks.

2. ## Re: Fill cells with day of month... (2000)

Sal
Does the following sequence of commands help?

<pre>?DateSerial(2008,3,1)
3/1/2008

?DateSerial(2008,3,1)-1
2/29/2008

?Day(DateSerial(2008,3,1)-1)
29
</pre>

3. ## Re: Fill cells with day of month... (2000)

I would add two comboboxes from the Controls toolbox -
cboMonth
cboYear.
plus a commandbutton to trigger the macro

Then place this code in the worksheet module

<pre>'---------------------------------------------------------------------------------------
' Module : Module1
' DateTime : 09/05/2007 08:43
' Author : Roy Cox (royUK)
' Website : www.excel-it.com for more examples and Excel Consulting
' Purpose : add dates to Column B based on combobox selections
' Disclaimer; This code is offered as is with no guarantees. You may use it in your
' projects but please leave this header intact.

'---------------------------------------------------------------------------------------
Option Explicit
Dim i As Long
Dim L As Long
Private Sub CommandButton1_Click()

i = Me.cboMonth.ListIndex + 1
Range(Cells(2, 2), Cells(31, 2)).Clear
Select Case i
Case 4, 6, 9, 11
L = 30
Case 2
If LeapYear(Me.cboYear.Value) Then
L = 29
Else: L = 28
End If
Case Else
L = 31
End Select
For i = 1 To L
Cells(4, 2 + i).Value = Format(DateSerial(CLng(Me.cboYear.Value), _
CLng(Me.cboMonth.ListIndex + 1), 0 + i), "dd.mm.yy")
Next i

End Sub

Private Sub Worksheet_Activate()

With Me
With .cboYear
.Clear
For i = 0 To 20
.AddItem (Format(Date, "YYYY")) + i
Next i
.ListIndex = 0
End With
.cboMonth.Clear
.cboMonth.List = Application.GetCustomListContents(4)
.cboMonth.ListIndex = Month(Date) - 1
L = .cboMonth.ListIndex

End With
End Sub</pre>

Then add this UDF to a Standard module

<pre>Option Explicit

Function LeapYear(year As Integer) As Boolean
If (Month(DateSerial(year, 2, 29)) = 2) Then
LeapYear = True
Else: LeapYear = False
End If
End Function</pre>

4. ## Re: Fill cells with day of month... (2000)

If this is on a sheet and and the Month combobox puts the month in A1 and the year in A2 you could put this in B4:
=IF(ISNUMBER(DATEVALUE(\$A\$1&" "&COLUMN()-1&", "&\$A\$2)),DATEVALUE(\$A\$1&" "&COLUMN()-1&", "&\$A\$2),"")

And copy it to C4:AF4

Steve

5. ## Re: Fill cells with day of month... (2000)

Good!
work for my project.
Tks.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•