Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    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>

    Regards
    Don

  3. #3
    New Lounger
    Join Date
    Sep 2005
    Location
    Derbyshire, United Kingdom
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •