Results 1 to 6 of 6

Thread: Ribbon DatePicker Calendar Control For Excel 2007-2010

  1. #1
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10

    Ribbon DatePicker Calendar Control For Excel 2007-2010

    EDIT: Please use the updated Date-picker calendar ribbon control that I posted in post #6

    I guess Calendar's are a great way to ensure that one doesn't key in a date in the wrong format, especially for the less cautious ones (I may add). For example it may get confusing when one wants to write 11th December and may eventually write 12th November. How easy would it be if we had a calendar that is readily available. Well, this is your lucky day. Here's a calendar that I've built for fun. I'm sure you'll like it.

    Ribbon Calendar.jpg

    If you are a developer, here's what you'll be interested in.

    Code:
    Option Explicit
    'This is our ribbon control variable
    Dim iruCalendar             As IRibbonUI
    'And this is where we have dimensioned our module variables
    'I'm just trying to be generous with the 64-bit users.
    'Why check for VB 7 environment if it's already a 64 bit environment, right? Yeah, I get that all the time. Go figure.
    'If you are a 'Winchester-Waco-Johnny-Dean-developer', you should look at this
    'http://social.msdn.microsoft.com/Forums/office/en-US/999c5d69-a176-43e5-b5df-716f8960fc6e/my-code-if-else-for-64-bit-is-not-being-recognized
    #If VBA7 Then
        #If Win64 Then
            Dim lngStartDay             As LongPtr, _
                lngEndDay               As LongPtr, _
                lngDayCount             As LongPtr, _
                lngDaySlotCount         As LongPtr, _
                lngSelectedYear         As LongPtr, _
                lngSelectedMonth        As LongPtr, _
                lngMonthDays(0 To 41)   As LongPtr
        #Else
            Dim lngStartDay             As Long, _
                lngEndDay               As Long, _
                lngDayCount             As Long, _
                lngDaySlotCount         As Long, _
                lngSelectedYear         As Long, _
                lngSelectedMonth        As Long, _
                lngMonthDays(0 To 41)   As Long
        #End If
    #Else 'Yes, it seems like a paradox, but who knows, what if it's 64 bit and still VB6
        Dim lngStartDay             As Long, _
            lngEndDay               As Long, _
            lngDayCount             As Long, _
            lngDaySlotCount         As Long, _
            lngSelectedYear         As Long, _
            lngSelectedMonth        As Long, _
            lngMonthDays(0 To 41)   As Long
    #End If
    'Callback for customUI.onLoad
    Sub LoadCalendar(ribbon As IRibbonUI)
        
        'So this is the first callback that will run for the ribbonUI, ie, the OnLoad function
        'We use an IRibbonUI control to set the object
        Set iruCalendar = ribbon
        'We also initialize our calendar control for the current year and date (so this is what the user will see by default)
        lngSelectedYear = Year(Date)
        lngSelectedMonth = Month(Date)
        
    End Sub
    
    'Callback for galCalendar getEnabled
    Sub GetEnabled(control As IRibbonControl, ByRef returnedVal)
        'Of course we want our calendar to be enabled
        'Having said that we could have avoided using the getEnabled feature, and just used enabled="true" in the XML
        returnedVal = True
    End Sub
    
    'Callback for galCalendar getItemCount
    Sub GetItemCount(control As IRibbonControl, ByRef returnedVal)
        'I use 42 because that's what all the normal date calendars use. So 42 is basically 7 days * 6 rows
        'The first row will be used to display week days Monday to Sunday
        'The next 6 rows will be used to display the dates depending on where the first date for the corresponding month starts from
        'For example, if we selected a non-leap year february, with the first day starting on a Monday, we would end up using only 4 rows
        'and the remaining 1 row will be entirely blank
        'Similarly, there will be months which start on a Sunday, which is the last column of our calendar control,
        'effectively using at 1 or 2 columns of the last row, depending on whether there are 30 or 31 days for that month
        returnedVal = 42
    End Sub
    
    'Callback for galCalendar getItemLabel
    Sub GetItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
        'So this is where all the action happens (well, at least most of it)
        'So first of all, we need the top row to have the names of the week
        'Of course it is up to the developer to decide on any algorithm to come up with the names of the weekdays
        'So I've defined a constant string with the names of the weekdays (abbreviated, and delimited)
        'Delimited so that I can split it using the split function, and assign to the respective column
        Const strDay As String = "Su|Mo|Tu|We|Th|Fr|Sa"
        'We now need to identify the weekday on which the first day of the month starts
        lngStartDay = Weekday(DateSerial(lngSelectedYear, lngSelectedMonth, 1)) 'Year(Date), Month(Date), 1))
        'We also need to know how many days there are in that month
        lngEndDay = Day(DateSerial(lngSelectedYear, lngSelectedMonth + 1, 0)) 'DateSerial(Year(Date), Month(Date) + 1, 0))
        'Now we use a select case to distinquish between the top row of our calendar, and the remaining rows
        'As you know, we have 7 columns. But what we need to be aware of is that the index parameter passed by this function starts from zero (0)
        'So in my select case, I used < 7 instead of <= 7
        Select Case index < 7
            Case True 'Of course you know what this means
                'Here we just pass back the name of each of the 7 weekdays as labels (yeah that's right, labels. Isn't that what the function name suggests?).
                'In other words, you can consider labels as a caption (you know, like for a commandbutton, or a userform. OK, you got the idea)
                returnedVal = Split(strDay, "|")(index) 'This is where we pass the name of the weekday as a label for the control
            Case Else 'Now, here's where the date part begins (because this is after the first 7 controls (or the top most row of our calendar)
                'You remember we had already captured the weekday on which the first day of the month starts
                'We also know how many days there are in the month
                'Now we need to keep track of how many controls we are iterating through. For that I simply use a variable and increment it
                'Note that the variables I am using have module scope (to know about scope of variables, visit http://support.microsoft.com/kb/141693)
                lngDaySlotCount = lngDaySlotCount + 1
                'So now we need to know when to start passing the days as labels
                'For that I'm also using another module variable to increment the days and check if the days haven't exceeded the maximum days in that month
                If lngDaySlotCount >= lngStartDay And lngDayCount < lngEndDay Then
                    lngDayCount = lngDayCount + 1 'This is the day increment variable
                    lngMonthDays(index) = lngDayCount 'This is an array of 42 items (0 to 41) where I keep track of the current months days. Will explain why I used this where I am using this
                    returnedVal = lngDayCount 'This is where we pass the day as a label for the control
                End If
        End Select
        
    End Sub
    
    'Callback for galCalendar onAction
    Sub galleryOnAction(control As IRibbonControl, id As String, index As Integer)
        'This is where we pass the value of the selected date, on to the sheet
        'Of course this will only pass value to the active cell. So if you've selected a range of cells, still the value will only be passed to the active cell
        'Using as On Error Resume Next statement just to ensure we don't loose the ribbon control due to unwanted errors (for example, if no workbook is active, then there wouldn't be an active cell, would there?)
        On Error Resume Next
        'Now, in the GetItemLabel callback that I used above, I am using an array that I use as a container for the labels of the 35 (42 - 7 top row) button items
        'I had mentioned that I'll explain it's usage later. Well, this is where I am using it.
        'To pass the value of the selected date, I am using the DateSerial function.
        'Now, we already know (or we will know) the selected year and the selected month
        'But that is not enough to pass the date. Yes, we need the day also. But we only know the index of the control that we pressed on
        'But since we have the values of the 35 items in the array, exactly in order of placement on the ribbon, we just need to refer to the value using the index we get as argument to this function
        'Oh and since some of the slots in the first row and the last row may be empty, we just need to check that before we actually pass the value
        If lngMonthDays(index) Then
            'So if lngMonthDays(index) is not zero, then it means it's a valid date for the selected month and year
            'And we pass that to the active cell
            ActiveCell.Value = DateSerial(lngSelectedYear, lngSelectedMonth, CInt(lngMonthDays(index)))
        Else
            'If lngMonthDays(index) is zero, then we just assume that the user clicked on the item by mistake and we just clear the activecell value
            'Of course we could just not do anything at all. But I thought what the heck, let the user have an extra reason to not be casual
            'If you want to be more empathetic (or sympathetic), just remove the line below)
            ActiveCell.ClearContents
        End If
    End Sub
    
    'Callback for galYear getItemLabel
    Sub GetItemLabelYear(control As IRibbonControl, index As Integer, ByRef returnedVal)
        'I probably am one of the most laziest person when it comes to programming.
        'So I just hard coded the year selection option to a window of 100 years (50 before and after current year)
        'And since index starts from 0, you can make out what the following line passes as returnedVal
        returnedVal = Year(Date) - 50 + index
    End Sub
    
    'Callback for galYear getItemCount
    Sub GetItemCountYear(control As IRibbonControl, ByRef returnedVal)
        'OK, so this is where I tell the ribbon that it's only have 100 items in the year selection gallery
        returnedVal = 50 * 2
    End Sub
    'Callback for galYear getLabel
    Sub GetLabelYear(control As IRibbonControl, ByRef returnedVal)
        'So whenever we need to pass a label (caption) to the year gallery, this is the function we use
        'You will remember that we are passing the current year to lngSelectedYear when the ribbon is loaded
        'But you'll also notice in one of the functions below that we are passing the user selected year also to this variable
        'That's where we keep the label dynamic (look at galleryOnActionYear function)
        returnedVal = lngSelectedYear
    End Sub
    
    'Callback for galMonth getLabel
    Sub GetLabelMonth(control As IRibbonControl, ByRef returnedVal)
        'So everybody who knows the MonthName fuction will know what this does. Those who don't just hit F1
        returnedVal = MonthName(lngSelectedMonth, True)
    End Sub
    
    'Callback for galYear onAction
    Sub galleryOnActionYear(control As IRibbonControl, id As String, index As Integer)
        'So here's where we convert the index value we receive when this function is invoked, in to the year which the user intended to select
        'Don't mind the word 'invoked'. It just meant 'called by the user by clicking on any of the 100 years'
        lngSelectedYear = Year(Date) - 50 + index
        'Here we do some cleaning and invalidation. Invalidating a control (or a ribbon) is like using the '.Dirty' function of a range object
        'It's like asking the control to validate itself again, cause we told it to do so ;)
        Call YearMonthChange
    End Sub
    
    'Callback for galMonth getItemLabel
    Sub GetItemLabelMonth(control As IRibbonControl, index As Integer, ByRef returnedVal)
        'You should be able to figure this one out by now. It follows the same principles as the GetItemLabelYear callback function
        returnedVal = MonthName(index + 1, True)
    End Sub
    
    'Callback for galMonth getItemCount
    Sub GetItemCountMonth(control As IRibbonControl, ByRef returnedVal)
        'Same logic as above
        returnedVal = 12
    End Sub
    
    'Callback for galMonth onAction
    Sub galleryOnActionMonth(control As IRibbonControl, id As String, index As Integer)
        'Index starts from 0. So if the user clicks to first item, we are supposed to get 1, not zero, so index + 1
        lngSelectedMonth = index + 1
        'Same logic as above
        YearMonthChange
    End Sub
    
    Sub YearMonthChange()
        
        'So we are invalidating the three controls (1)Day, (2)Year, (3)Month
        iruCalendar.InvalidateControl "galCalendar"
        iruCalendar.InvalidateControl "galYear"
        iruCalendar.InvalidateControl "galMonth"
        'We are also resetting our Day and DaySlot counters to empty
        lngDayCount = Empty
        lngDaySlotCount = Empty
        'And since each month is different from each, we cannot hold the container constant
        'It's an array, and we need to clear it using the Erase function
        Erase lngMonthDays
        
    End Sub
    Attached Files Attached Files

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    And here's the XML for the RibbonUI


    PHP Code:
    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="LoadCalendar">
       <
    ribbon startFromScratch="false">
          <
    tabs>
             <
    tab idMso="TabInsert" >
                <
    group id="grpCalendar" keytip="C" insertBeforeMso="GroupInsertTablesExcel" label="Calendar">
                   <
    gallery id="galCalendar" 
                
    size="large"
                      
    columns="7" 
                      
    rows="7"
                
    label="Day"
                
    screentip="You can use this to select any day on the calendar"
                      
    supertip="To change month and year, select the month and year from the adjacent dropdown items"
                
    keytip "D"
                      
    image="Calendar"
                      
    getItemLabel="GetItemLabel"
                
    getItemCount="GetItemCount"
                      
    onAction="galleryOnAction">
                         <
    item id="itmDay01"/>    
                         <
    item id="itmDay02"/>    
                         <
    item id="itmDay03"/>    
                         <
    item id="itmDay04"/>    
                         <
    item id="itmDay05"/>    
                         <
    item id="itmDay06"/>    
                         <
    item id="itmDay07"/>
                         <
    item id="itm01"/>    
                         <
    item id="itm02"/>    
                         <
    item id="itm03"/>    
                         <
    item id="itm04"/>    
                         <
    item id="itm05"/>    
                         <
    item id="itm06"/>    
                         <
    item id="itm07"/>    
                         <
    item id="itm08"/>    
                         <
    item id="itm09"/>    
                         <
    item id="itm10"/>    
                         <
    item id="itm11"/>    
                         <
    item id="itm12"/>    
                         <
    item id="itm13"/>    
                         <
    item id="itm14"/>    
                         <
    item id="itm15"/>    
                         <
    item id="itm16"/>    
                         <
    item id="itm17"/>    
                         <
    item id="itm18"/>    
                         <
    item id="itm19"/>    
                         <
    item id="itm20"/>    
                         <
    item id="itm21"/>    
                         <
    item id="itm22"/>    
                         <
    item id="itm23"/>    
                         <
    item id="itm24"/>    
                         <
    item id="itm25"/>    
                         <
    item id="itm26"/>    
                         <
    item id="itm27"/>    
                         <
    item id="itm28"/>    
                         <
    item id="itm29"/>    
                         <
    item id="itm30"/>    
                         <
    item id="itm31"/>    
                         <
    item id="itm32"/>    
                         <
    item id="itm33"/>    
                         <
    item id="itm34"/>    
                         <
    item id="itm35"/>    
                         <
    item id="itm36"/>    
                         <
    item id="itm37"/>    
                         <
    item id="itm38"/>    
                         <
    item id="itm39"/>    
                         <
    item id="itm40"/>    
                         <
    item id="itm41"/>    
                         <
    item id="itm42"/>    
                   </
    gallery>
                <
    box id="boxH01" boxStyle="horizontal">
               <
    gallery id="galMonth"
                      
    columns="4" 
                      
    rows="3"
                
    getLabel="GetLabelMonth"
                
    screentip="You can use this to select any month on the calendar"
                      
    supertip="To change day and year, select the day and year from the adjacent dropdown items"
                
    keytip "M"
                      
    getItemLabel="GetItemLabelMonth"
                
    getItemCount="GetItemCountMonth"
                      
    onAction="galleryOnActionMonth">
                         <
    item id="itmMon01"/>    
                         <
    item id="itmMon02"/>    
                         <
    item id="itmMon03"/>    
                         <
    item id="itmMon04"/>    
                         <
    item id="itmMon05"/>    
                         <
    item id="itmMon06"/>    
                         <
    item id="itmMon07"/>
                         <
    item id="itmMon08"/>    
                         <
    item id="itmMon09"/>    
                         <
    item id="itmMon10"/>    
                         <
    item id="itmMon11"/>    
                         <
    item id="itmMon12"/>
                   </
    gallery>
               <
    gallery id="galYear"
                      
    columns="10" 
                      
    rows="10"
                
    getLabel="GetLabelYear"
                
    screentip="You can use this to select any year between 50 years before or after this year"
                      
    supertip="To change day and month, select the day and month from the adjacent dropdown items"
                
    keytip "Y"
                      
    getItemLabel="GetItemLabelYear"
                
    getItemCount="GetItemCountYear"
                      
    onAction="galleryOnActionYear">
                         <
    item id="itmYear001"/>    
                         <
    item id="itmYear002"/>    
                         <
    item id="itmYear003"/>    
                         <
    item id="itmYear004"/>    
                         <
    item id="itmYear005"/>    
                         <
    item id="itmYear006"/>    
                         <
    item id="itmYear007"/>    
                         <
    item id="itmYear008"/>    
                         <
    item id="itmYear009"/>    
                         <
    item id="itmYear010"/>    
                         <
    item id="itmYear011"/>    
                         <
    item id="itmYear012"/>    
                         <
    item id="itmYear013"/>    
                         <
    item id="itmYear014"/>    
                         <
    item id="itmYear015"/>    
                         <
    item id="itmYear016"/>    
                         <
    item id="itmYear017"/>    
                         <
    item id="itmYear018"/>    
                         <
    item id="itmYear019"/>    
                         <
    item id="itmYear020"/>    
                         <
    item id="itmYear021"/>    
                         <
    item id="itmYear022"/>    
                         <
    item id="itmYear023"/>    
                         <
    item id="itmYear024"/>    
                         <
    item id="itmYear025"/>    
                         <
    item id="itmYear026"/>    
                         <
    item id="itmYear027"/>    
                         <
    item id="itmYear028"/>    
                         <
    item id="itmYear029"/>    
                         <
    item id="itmYear030"/>    
                         <
    item id="itmYear031"/>    
                         <
    item id="itmYear032"/>    
                         <
    item id="itmYear033"/>    
                         <
    item id="itmYear034"/>    
                         <
    item id="itmYear035"/>    
                         <
    item id="itmYear036"/>    
                         <
    item id="itmYear037"/>    
                         <
    item id="itmYear038"/>    
                         <
    item id="itmYear039"/>    
                         <
    item id="itmYear040"/>    
                         <
    item id="itmYear041"/>    
                         <
    item id="itmYear042"/>    
                         <
    item id="itmYear043"/>    
                         <
    item id="itmYear044"/>    
                         <
    item id="itmYear045"/>    
                         <
    item id="itmYear046"/>    
                         <
    item id="itmYear047"/>    
                         <
    item id="itmYear048"/>    
                         <
    item id="itmYear049"/>    
                         <
    item id="itmYear050"/>    
                         <
    item id="itmYear051"/>    
                         <
    item id="itmYear052"/>    
                         <
    item id="itmYear053"/>    
                         <
    item id="itmYear054"/>    
                         <
    item id="itmYear055"/>    
                         <
    item id="itmYear056"/>    
                         <
    item id="itmYear057"/>    
                         <
    item id="itmYear058"/>    
                         <
    item id="itmYear059"/>    
                         <
    item id="itmYear060"/>    
                         <
    item id="itmYear061"/>    
                         <
    item id="itmYear062"/>    
                         <
    item id="itmYear063"/>    
                         <
    item id="itmYear064"/>    
                         <
    item id="itmYear065"/>    
                         <
    item id="itmYear066"/>    
                         <
    item id="itmYear067"/>    
                         <
    item id="itmYear068"/>    
                         <
    item id="itmYear069"/>    
                         <
    item id="itmYear070"/>    
                         <
    item id="itmYear071"/>    
                         <
    item id="itmYear072"/>    
                         <
    item id="itmYear073"/>    
                         <
    item id="itmYear074"/>    
                         <
    item id="itmYear075"/>    
                         <
    item id="itmYear076"/>    
                         <
    item id="itmYear077"/>    
                         <
    item id="itmYear078"/>    
                         <
    item id="itmYear079"/>    
                         <
    item id="itmYear080"/>    
                         <
    item id="itmYear081"/>    
                         <
    item id="itmYear082"/>    
                         <
    item id="itmYear083"/>    
                         <
    item id="itmYear084"/>    
                         <
    item id="itmYear085"/>    
                         <
    item id="itmYear086"/>    
                         <
    item id="itmYear087"/>    
                         <
    item id="itmYear088"/>    
                         <
    item id="itmYear089"/>    
                         <
    item id="itmYear090"/>    
                         <
    item id="itmYear091"/>    
                         <
    item id="itmYear092"/>    
                         <
    item id="itmYear093"/>    
                         <
    item id="itmYear094"/>    
                         <
    item id="itmYear095"/>    
                         <
    item id="itmYear096"/>    
                         <
    item id="itmYear097"/>    
                         <
    item id="itmYear098"/>    
                         <
    item id="itmYear099"/>    
                         <
    item id="itmYear100"/>    
                         <
    item id="itmYear101"/>    
                   </
    gallery>
                </
    box>
                </
    group>
             </
    tab>
          </
    tabs>
       </
    ribbon>
    </
    customUI
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Based on some feedback I've received, here's an updated version of the date-picker. You can read about the discussions here at http://chandoo.org/forum/threads/rib...07-2010.12150/

    PHP Code:
    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="LoadCalendar">
       <
    ribbon startFromScratch="false">
          <
    tabs>
             <
    tab idMso="TabInsert" >
                <
    group id="grpCalendar" keytip="C" insertBeforeMso="GroupInsertTablesExcel" getLabel="GetLabelCalendarGroup">
                   <
    gallery id="galCalendar" 
                
    size="large"
                      
    columns="7" 
                      
    rows="7"
                
    getLabel="GetLabelDay"
                
    screentip="You can use this to select any day on the calendar"
                      
    supertip="To change month and year, select the month and year from the adjacent dropdown items"
                
    keytip "D"
                      
    image="Calendar"
                      
    getItemLabel="GetItemLabel"
                
    getItemCount="GetItemCount"
                      
    onAction="galleryOnAction">
                         <
    item id="itmDay01"/>    
                         <
    item id="itmDay02"/>    
                         <
    item id="itmDay03"/>    
                         <
    item id="itmDay04"/>    
                         <
    item id="itmDay05"/>    
                         <
    item id="itmDay06"/>    
                         <
    item id="itmDay07"/>
                         <
    item id="itm01"/>    
                         <
    item id="itm02"/>    
                         <
    item id="itm03"/>    
                         <
    item id="itm04"/>    
                         <
    item id="itm05"/>    
                         <
    item id="itm06"/>    
                         <
    item id="itm07"/>    
                         <
    item id="itm08"/>    
                         <
    item id="itm09"/>    
                         <
    item id="itm10"/>    
                         <
    item id="itm11"/>    
                         <
    item id="itm12"/>    
                         <
    item id="itm13"/>    
                         <
    item id="itm14"/>    
                         <
    item id="itm15"/>    
                         <
    item id="itm16"/>    
                         <
    item id="itm17"/>    
                         <
    item id="itm18"/>    
                         <
    item id="itm19"/>    
                         <
    item id="itm20"/>    
                         <
    item id="itm21"/>    
                         <
    item id="itm22"/>    
                         <
    item id="itm23"/>    
                         <
    item id="itm24"/>    
                         <
    item id="itm25"/>    
                         <
    item id="itm26"/>    
                         <
    item id="itm27"/>    
                         <
    item id="itm28"/>    
                         <
    item id="itm29"/>    
                         <
    item id="itm30"/>    
                         <
    item id="itm31"/>    
                         <
    item id="itm32"/>    
                         <
    item id="itm33"/>    
                         <
    item id="itm34"/>    
                         <
    item id="itm35"/>    
                         <
    item id="itm36"/>    
                         <
    item id="itm37"/>    
                         <
    item id="itm38"/>    
                         <
    item id="itm39"/>    
                         <
    item id="itm40"/>    
                         <
    item id="itm41"/>    
                         <
    item id="itm42"/>    
                   </
    gallery>
                <
    box id="boxH01" boxStyle="horizontal">
                <
    button id="btnMonthDecrement"
                    
    imageMso "ShapeLeftArrow"
                    
    onAction="MonthDecrement"/>
               <
    gallery id="galMonth"
                      
    columns="4" 
                      
    rows="3"
                
    getLabel="GetLabelMonth"
                
    screentip="You can use this to select any month on the calendar"
                      
    supertip="To change day and year, select the day and year from the adjacent dropdown items"
                
    keytip "M"
                      
    getItemLabel="GetItemLabelMonth"
                
    getItemCount="GetItemCountMonth"
                      
    onAction="galleryOnActionMonth">
                         <
    item id="itmMon01"/>    
                         <
    item id="itmMon02"/>    
                         <
    item id="itmMon03"/>    
                         <
    item id="itmMon04"/>    
                         <
    item id="itmMon05"/>    
                         <
    item id="itmMon06"/>    
                         <
    item id="itmMon07"/>
                         <
    item id="itmMon08"/>    
                         <
    item id="itmMon09"/>    
                         <
    item id="itmMon10"/>    
                         <
    item id="itmMon11"/>    
                         <
    item id="itmMon12"/>
                   </
    gallery>
                <
    button id="btnMonthIncrement"
                    
    imageMso "ShapeRightArrow"
                    
    onAction="MonthIncrement"/>
                </
    box>
        <
    box id="boxH02" boxStyle="horizontal">
                <
    button id="btnYearBigDecrement"
                    
    imageMso "FillLeft"
                    
    onAction="YearBigDecrement"/>
                <
    button id="btnYearSmallDecrement"
                    
    imageMso "ViewGoBack"
                    
    onAction="YearSmallDecrement"/>
               <
    gallery id="galYear"
                      
    columns="10" 
                      
    rows="10"
                
    getLabel="GetLabelYear"
                
    screentip="You can use this to select any year between 50 years before or after this year"
                      
    supertip="To change day and month, select the day and month from the adjacent dropdown items"
                
    keytip "Y"
                      
    getItemLabel="GetItemLabelYear"
                
    getItemCount="GetItemCountYear"
                      
    onAction="galleryOnActionYear">
                         <
    item id="itmYear001"/>    
                         <
    item id="itmYear002"/>    
                         <
    item id="itmYear003"/>    
                         <
    item id="itmYear004"/>    
                         <
    item id="itmYear005"/>    
                         <
    item id="itmYear006"/>    
                         <
    item id="itmYear007"/>    
                         <
    item id="itmYear008"/>    
                         <
    item id="itmYear009"/>    
                         <
    item id="itmYear010"/>    
                         <
    item id="itmYear011"/>    
                         <
    item id="itmYear012"/>    
                         <
    item id="itmYear013"/>    
                         <
    item id="itmYear014"/>    
                         <
    item id="itmYear015"/>    
                         <
    item id="itmYear016"/>    
                         <
    item id="itmYear017"/>    
                         <
    item id="itmYear018"/>    
                         <
    item id="itmYear019"/>    
                         <
    item id="itmYear020"/>    
                         <
    item id="itmYear021"/>    
                         <
    item id="itmYear022"/>    
                         <
    item id="itmYear023"/>    
                         <
    item id="itmYear024"/>    
                         <
    item id="itmYear025"/>    
                         <
    item id="itmYear026"/>    
                         <
    item id="itmYear027"/>    
                         <
    item id="itmYear028"/>    
                         <
    item id="itmYear029"/>    
                         <
    item id="itmYear030"/>    
                         <
    item id="itmYear031"/>    
                         <
    item id="itmYear032"/>    
                         <
    item id="itmYear033"/>    
                         <
    item id="itmYear034"/>    
                         <
    item id="itmYear035"/>    
                         <
    item id="itmYear036"/>    
                         <
    item id="itmYear037"/>    
                         <
    item id="itmYear038"/>    
                         <
    item id="itmYear039"/>    
                         <
    item id="itmYear040"/>    
                         <
    item id="itmYear041"/>    
                         <
    item id="itmYear042"/>    
                         <
    item id="itmYear043"/>    
                         <
    item id="itmYear044"/>    
                         <
    item id="itmYear045"/>    
                         <
    item id="itmYear046"/>    
                         <
    item id="itmYear047"/>    
                         <
    item id="itmYear048"/>    
                         <
    item id="itmYear049"/>    
                         <
    item id="itmYear050"/>    
                         <
    item id="itmYear051"/>    
                         <
    item id="itmYear052"/>    
                         <
    item id="itmYear053"/>    
                         <
    item id="itmYear054"/>    
                         <
    item id="itmYear055"/>    
                         <
    item id="itmYear056"/>    
                         <
    item id="itmYear057"/>    
                         <
    item id="itmYear058"/>    
                         <
    item id="itmYear059"/>    
                         <
    item id="itmYear060"/>    
                         <
    item id="itmYear061"/>    
                         <
    item id="itmYear062"/>    
                         <
    item id="itmYear063"/>    
                         <
    item id="itmYear064"/>    
                         <
    item id="itmYear065"/>    
                         <
    item id="itmYear066"/>    
                         <
    item id="itmYear067"/>    
                         <
    item id="itmYear068"/>    
                         <
    item id="itmYear069"/>    
                         <
    item id="itmYear070"/>    
                         <
    item id="itmYear071"/>    
                         <
    item id="itmYear072"/>    
                         <
    item id="itmYear073"/>    
                         <
    item id="itmYear074"/>    
                         <
    item id="itmYear075"/>    
                         <
    item id="itmYear076"/>    
                         <
    item id="itmYear077"/>    
                         <
    item id="itmYear078"/>    
                         <
    item id="itmYear079"/>    
                         <
    item id="itmYear080"/>    
                         <
    item id="itmYear081"/>    
                         <
    item id="itmYear082"/>    
                         <
    item id="itmYear083"/>    
                         <
    item id="itmYear084"/>    
                         <
    item id="itmYear085"/>    
                         <
    item id="itmYear086"/>    
                         <
    item id="itmYear087"/>    
                         <
    item id="itmYear088"/>    
                         <
    item id="itmYear089"/>    
                         <
    item id="itmYear090"/>    
                         <
    item id="itmYear091"/>    
                         <
    item id="itmYear092"/>    
                         <
    item id="itmYear093"/>    
                         <
    item id="itmYear094"/>    
                         <
    item id="itmYear095"/>    
                         <
    item id="itmYear096"/>    
                         <
    item id="itmYear097"/>    
                         <
    item id="itmYear098"/>    
                         <
    item id="itmYear099"/>    
                         <
    item id="itmYear100"/>    
                         <
    item id="itmYear101"/>    
                   </
    gallery>
                <
    button id="btnYearSmallIncrement"
                    
    imageMso "ViewGoForward"
                    
    onAction="YearSmallIncrement"/>
                <
    button id="btnYearBigIncrement"
                    
    imageMso "FillRight"
                    
    onAction="YearBigIncrement"/>
                </
    box>
                </
    group>
             </
    tab>
          </
    tabs>
       </
    ribbon>
    </
    customUI
    And the new date-picker looks like this
    Ribbon Calendar Revised.jpg
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Code:
    Option Explicit
    '===========================================================================================
    'An idea remains an idea until it is implemented
    'Due credit to Neil Holder for his solution here at
    'http://www.excelfox.com/forum/f12/ribbon-calendar-datepicker-word-2007-2010-a-57/#post6194
    '===========================================================================================
    
    'This is our ribbon control variable
    Dim miruCalendar As IRibbonUI
    
    Private Const mcbytDayOfWeek As Byte = vbUseSystemDayOfWeek
    'Changing this value will allow you to specific the big increment/decrement for years
    Private Const mclngBigYearIncrementDecrement As Long = 10
    Private Const mclngYearWindowFromSelectedYear As Long = 50
    #If VBA7 Then
        #If Win64 Then
            Dim lngStartDay             As LongPtr, _
                lngEndDay               As LongPtr, _
                lngDayCount             As LongPtr, _
                lngDaySlotCount         As LongPtr, _
                lngSelectedYear         As LongPtr, _
                lngSelectedMonth        As LongPtr, _
                lngMonthDays(0 To 48)   As LongPtr
        #Else
            Dim lngStartDay             As Long, _
                lngEndDay               As Long, _
                lngDayCount             As Long, _
                lngDaySlotCount         As Long, _
                lngSelectedYear         As Long, _
                lngSelectedMonth        As Long, _
                lngMonthDays(0 To 48)   As Long
        #End If
    #Else 'Yes, it seems like a paradox, but who knows, what if it's 64 bit and still VB6
        Dim lngStartDay             As Long, _
            lngEndDay               As Long, _
            lngDayCount             As Long, _
            lngDaySlotCount         As Long, _
            lngSelectedYear         As Long, _
            lngSelectedMonth        As Long, _
            lngMonthDays(0 To 48)   As Long
    #End If
    'Callback for customUI.onLoad
    Sub LoadCalendar(ribbon As IRibbonUI)
        
        'So this is the first callback that will run for the ribbonUI, ie, the OnLoad function
        'We use an IRibbonUI control to set the object
        Set miruCalendar = ribbon
        'We also initialize our calendar control for the current year and date (so this is what the user will see by default)
        lngSelectedYear = Year(Date)
        lngSelectedMonth = Month(Date)
        
    End Sub
    
    'Callback for grpCalendar getLabel
    Sub GetLabelCalendarGroup(control As IRibbonControl, ByRef returnedVal)
        returnedVal = "Calendar"
    End Sub
    
    'Callback for galCalendar getEnabled
    Sub GetEnabled(control As IRibbonControl, ByRef returnedVal)
        'Of course we want our calendar to be enabled
        'Having said that we could have avoided using the getEnabled feature, and just used enabled="true" in the XML
        returnedVal = True
    End Sub
    'Callback for galCalendar getLabel
    Sub GetLabelDay(control As IRibbonControl, ByRef returnedVal)
        'If you want to provide any label (caption) for the date selection gallery, you can pass that value here
        'Note the some special characters are not accepted
        returnedVal = "Day"
    End Sub
    'Callback for galCalendar getItemCount
    Sub GetItemCount(control As IRibbonControl, ByRef returnedVal)
        'I use 49 because that's what all the normal date calendars use.  After the top row is used for the name of the 7 days, 42 remains.
        'So 42 is basically 7 days * 6 rows
        'The first row will be used to display week days Monday to Sunday
        'The next 6 rows will be used to display the dates depending on where the first date for the corresponding month starts from
        'For example, if we selected a non-leap year february, with the  first day starting on a Monday, we would end up using only 4 rows
        'and the remaining 1 row will be entirely blank
        'Similarly, there will be months which start on a Sunday, which is the last column of our calendar control,
        'effectively using at 1 or 2 columns of the last row, depending on whether there are 30 or 31 days for that month
        returnedVal = 49
    End Sub
    
    'Callback for galCalendar getItemLabel
    Sub GetItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
        'So this is where all the action happens (well, at least most of it)
        'So first of all, we need the top row to have the names of the week
        'Of course it is up to the developer to decide on any algorithm to come up with the names of the weekdays
        'Based on feedback from some non-English office users,
        'I've decided to use the WeekDayName function to return the correct Weekday name depending on which day their week starts
        'We now need to identify the weekday on which the first day of the month starts
        lngStartDay = Weekday(DateSerial(lngSelectedYear, lngSelectedMonth, 1), mcbytDayOfWeek) 'Year(Date), Month(Date), 1))
        'We also need to know how many days there are in that month
        lngEndDay = Day(DateSerial(lngSelectedYear, lngSelectedMonth + 1, 0)) 'DateSerial(Year(Date), Month(Date) + 1, 0))
        'Now we use a select case to distinquish between the top row of our calendar, and the remaining rows
        'As you know, we have 7 columns. But what we need to be aware of is  that the index parameter passed by this function starts from zero (0)
        'So in my select case, I used < 7 instead of <= 7
        Select Case index < 7
            Case True 'Of course you know what this means
                'Here we just pass back the name of each of the 7 weekdays  as labels (yeah that's right, labels. Isn't that what the function name  suggests?).
                'In other words, you can consider labels as a caption (you  know, like for a commandbutton, or a userform. OK, you got the idea)
                returnedVal = Left(WeekdayName(index + 1, True,  mcbytDayOfWeek), 2) 'This is where we pass the name of the weekday as a  label for the control
            Case Else 'Now, here's where the date part begins (because this  is after the first 7 controls (or the top most row of our calendar)
                'You remember we had already captured the weekday on which the first day of the month starts
                'We also know how many days there are in the month
                'Now we need to keep track of how many controls we are  iterating through. For that I simply use a variable and increment it
                'Note that the variables I am using have module scope (to  know about scope of variables, visit  http://support.microsoft.com/kb/141693)
                lngDaySlotCount = lngDaySlotCount + 1
                'So now we need to know when to start passing the days as labels
                'For that I'm also using another module variable to  increment the days and check if the days haven't exceeded the maximum  days in that month
                If lngDaySlotCount >= lngStartDay And lngDayCount < lngEndDay Then
                    lngDayCount = lngDayCount + 1 'This is the day increment variable
                    lngMonthDays(index) = lngDayCount 'This is an array of  49 items (0 to 48) where I keep track of the current months days. Will  explain why I used this where I am using this
                    returnedVal = lngDayCount 'This is where we pass the day as a label for the control
                End If
        End Select
        
    End Sub
    
    'Callback for galCalendar onAction
    Sub galleryOnAction(control As IRibbonControl, id As String, index As Integer)
        'This is where we pass the value of the selected date, on to the sheet
        'Of course this will only pass value to the active cell. So if  you've selected a range of cells, still the value will only be passed to  the active cell
        'Using as On Error Resume Next statement just to ensure we don't  loose the ribbon control due to unwanted errors (for example, if no  workbook is active, then there wouldn't be an active cell, would there?)
        On Error Resume Next
        'Now, in the GetItemLabel callback that I used above, I am using an  array that I use as a container for the labels of the 42 (49 - 7 top  row) button items
        'I had mentioned that I'll explain it's usage later. Well, this is where I am using it.
        'To pass the value of the selected date, I am using the DateSerial function.
        'Now, we already know (or we will know) the selected year and the selected month
        'But that is not enough to pass the date. Yes, we need the day also.  But we only know the index of the control that we pressed on
        'But since we have the values of the 42 items in the array, exactly  in order of placement on the ribbon, we just need to refer to the value  using the index we get as argument to this function
        'Oh and since some of the slots in the first row and the last row  may be empty, we just need to check that before we actually pass the  value
        If lngMonthDays(index) Then
            'So if lngMonthDays(index) is not zero, then it means it's a valid date for the selected month and year
            'And we pass that to the active cell
            ActiveCell.Value = DateSerial(lngSelectedYear, lngSelectedMonth, CInt(lngMonthDays(index)))
        Else
            'If lngMonthDays(index) is zero, then we just assume that the  user clicked on the item by mistake and we just clear the activecell  value
            'Of course we could just not do anything at all. But I thought  what the heck, let the user have an extra reason to not be casual
            'If you want to be more empathetic (or sympathetic), just remove the line below)
            ActiveCell.ClearContents
        End If
    End Sub
    
    'Callback for galYear getItemLabel
    Sub GetItemLabelYear(control As IRibbonControl, index As Integer, ByRef returnedVal)
        'I probably am one of the most laziest person when it comes to programming.
        'So I just hard coded the year selection option to a window of  mclngYearWindowFromSelectedYear * 2 years (ie,  mclngYearWindowFromSelectedYear before and after current year)
        'And since index starts from 0, you can make out what the following line passes as returnedVal
        returnedVal = Year(Date) - mclngYearWindowFromSelectedYear + index
    End Sub
    
    'Callback for galYear getItemCount
    Sub GetItemCountYear(control As IRibbonControl, ByRef returnedVal)
        'OK, so this is where I tell the ribbon that it should only have X * 2 items in the year selection gallery
        returnedVal = mclngYearWindowFromSelectedYear * 2
    End Sub
    'Callback for galYear getLabel
    Sub GetLabelYear(control As IRibbonControl, ByRef returnedVal)
        'So whenever we need to pass a label (caption) to the year gallery, this is the function we use
        'You will remember that we are passing the current year to lngSelectedYear when the ribbon is loaded
        'But you'll also notice in one of the functions below that we are passing the user selected year also to this variable
        'That's where we keep the label dynamic (look at galleryOnActionYear function)
        returnedVal = lngSelectedYear
    End Sub
    
    'Callback for btnMonthDecrement onAction
    Sub MonthDecrement(control As IRibbonControl)
        If lngSelectedMonth = 1 Then
            lngSelectedMonth = 13
            lngSelectedYear = lngSelectedYear - 1
        End If
        lngSelectedMonth = lngSelectedMonth - 1
        YearMonthChange
    End Sub
    
    'Callback for galMonth getLabel
    Sub GetLabelMonth(control As IRibbonControl, ByRef returnedVal)
        'So everybody who knows the MonthName fuction will know what this does. Those who don't just hit F1
        returnedVal = MonthName(lngSelectedMonth, True)
    End Sub
    
    'Callback for btnMonthIncrement onAction
    Sub MonthIncrement(control As IRibbonControl)
    
        If lngSelectedMonth = 12 Then
            lngSelectedMonth = 0
            lngSelectedYear = lngSelectedYear + 1
        End If
        lngSelectedMonth = lngSelectedMonth + 1
        YearMonthChange
        
    End Sub
    
    
    'Callback for galMonth getItemLabel
    Sub GetItemLabelMonth(control As IRibbonControl, index As Integer, ByRef returnedVal)
        'You should be able to figure this one out by now. It follows the same principles as the GetItemLabelYear callback function
        returnedVal = MonthName(index + 1, True)
    End Sub
    
    'Callback for galMonth getItemCount
    Sub GetItemCountMonth(control As IRibbonControl, ByRef returnedVal)
        'Same logic as above
        returnedVal = 12
    End Sub
    
    'Callback for galMonth onAction
    Sub galleryOnActionMonth(control As IRibbonControl, id As String, index As Integer)
        'Index starts from 0. So if the user clicks to first item, we are supposed to get 1, not zero, so index + 1
        lngSelectedMonth = index + 1
        'Same logic as above
        YearMonthChange
    End Sub
    
    'Callback for btnYearBigDecrement onAction
    Sub YearBigDecrement(control As IRibbonControl)
        'This will decrement the selected year by X (as defined by the constant mclngBigYearIncrementDecrement
        lngSelectedYear = lngSelectedYear - mclngBigYearIncrementDecrement
        'Calling the invalidation and clean-up routine whenever a year/month changes
        YearMonthChange
    End Sub
    
    'Callback for btnYearSmallDecrement onAction
    Sub YearSmallDecrement(control As IRibbonControl)
    'This will decrement the selected year by 1
        lngSelectedYear = lngSelectedYear - 1
        'Calling the invalidation and clean-up routine whenever a year/month changes
        YearMonthChange
    End Sub
    
    'Callback for galYear onAction
    Sub galleryOnActionYear(control As IRibbonControl, id As String, index As Integer)
        'So here's where we convert the index value we receive when this  function is invoked, in to the year which the user intended to select
        'Don't mind the word 'invoked'. It just meant 'called by the user by  clicking on any of the mclngYearWindowFromSelectedYear * 2 years'
        lngSelectedYear = Year(Date) - mclngYearWindowFromSelectedYear + index
        'Here we do some cleaning and invalidation. Invalidating a control  (or a ribbon) is like using the '.Dirty' function of a range object
        'It's like asking the control to validate itself again, cause we told it to do so ;)
        Call YearMonthChange
    End Sub
    
    'Callback for btnYearSmallIncrement onAction
    Sub YearSmallIncrement(control As IRibbonControl)
        'This will increment the selected year by 1
        lngSelectedYear = lngSelectedYear + 1
        'Calling the invalidation and clean-up routine whenever a year/month changes
        YearMonthChange
    End Sub
    
    'Callback for btnYearBigIncrement onAction
    Sub YearBigIncrement(control As IRibbonControl)
        'This will increment the selected year by X (as defined by the constant mclngBigYearIncrementDecrement
        lngSelectedYear = lngSelectedYear + mclngBigYearIncrementDecrement
        'Calling the invalidation and clean-up routine whenever a year/month changes
        YearMonthChange
    End Sub
    Sub YearMonthChange()
        
        'So we are invalidating the three controls (1)Day, (2)Year, (3)Month
        miruCalendar.InvalidateControl "galCalendar"
        miruCalendar.InvalidateControl "galYear"
        miruCalendar.InvalidateControl "galMonth"
        'We are also resetting our Day and DaySlot counters to empty
        lngDayCount = Empty
        lngDaySlotCount = Empty
        'And since each month is different from each, we cannot hold the container constant
        'It's an array, and we need to clear it using the Erase function
        Erase lngMonthDays
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Feedback from Dexter1759

    Thanks for the compliments regarding my calendar.


    Unfortunately, I can't get your's working on Excel 2010 64 bit. It opens but when I click on the Insert tab the following procedures generates a Type Mismatch error:


    Code:
      Sub GetLabelMonth(control As IRibbonControl, ByRef returnedVal)
    
        'So everybody who knows the MonthName fuction will know what this does. Those who don't just hit F1
        returnedVal = MonthName(lngSelectedMonth, True)
      End Sub
    With "lngSelectedMonth" highlighted as the culprit. I'm not all that experienced with Excel 2010/64 bit, but I'm aware of the use of "#If VBA7 Then" to define variables for 64 bit vs 32 bit. It looks like despite having Win 7 64bit and Excel 2010 64bit, anywhere "lngSelectedMonth" or "lngSelectedYear" is used, the functions are still expecting a Long not LongPtr and wrapping them in Clng seems to fix the issue, but I'm not sure if that is just for my version/set up. Not sure it would work for anyone else.
    This is something I overlooked. Thanks Dexter.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Couldn't spend time on working out Dexter's solution as I was very busy over the last 10 days. I've made modifications to the code. And here's the revised file. And the code below.

    Code:
    Option Explicit
    Dim miruCalendar As IRibbonUI
    Private Const mcbytDayOfWeek As Byte = vbUseSystemDayOfWeek
    'Changing this value will allow you to specific the big increment/decrement for years
    Private Const mclngBigYearIncrementDecrement As Long = 10
    Private Const mclngYearWindowFromSelectedYear As Long = 50
    #If VBA7 Then
        #If Win64 Then
            Dim lngStartDay             As LongPtr, _
                lngEndDay               As LongPtr, _
                lngDayCount             As LongPtr, _
                lngDaySlotCount         As LongPtr, _
                lngSelectedYear         As LongPtr, _
                lngSelectedMonth        As LongPtr, _
                lngMonthDays(0 To 48)   As LongPtr
        #Else
            Dim lngStartDay             As Long, _
                lngEndDay               As Long, _
                lngDayCount             As Long, _
                lngDaySlotCount         As Long, _
                lngSelectedYear         As Long, _
                lngSelectedMonth        As Long, _
                lngMonthDays(0 To 48)   As Long
        #End If
    #Else 'Yes, it seems like a paradox, but who knows, what if it's 64 bit and still VB6
        Dim lngStartDay             As Long, _
            lngEndDay               As Long, _
            lngDayCount             As Long, _
            lngDaySlotCount         As Long, _
            lngSelectedYear         As Long, _
            lngSelectedMonth        As Long, _
            lngMonthDays(0 To 48)   As Long
    #End If
    'Callback for customUI.onLoad
    Sub LoadCalendar(ribbon As IRibbonUI)
        
        'So this is the first callback that will run for the ribbonUI, ie, the OnLoad function
        'We use an IRibbonUI control to set the object
        Set miruCalendar = ribbon
        'We also initialize our calendar control for the current year and date (so this is what the user will see by default)
        lngSelectedYear = Year(Date)
        lngSelectedMonth = Month(Date)
        
    End Sub
    
    'Callback for grpCalendar getLabel
    Sub GetLabelCalendarGroup(control As IRibbonControl, ByRef returnedVal)
        returnedVal = "Calendar"
    End Sub
    
    'Callback for galCalendar getEnabled
    Sub GetEnabled(control As IRibbonControl, ByRef returnedVal)
        'Of course we want our calendar to be enabled
        'Having said that we could have avoided using the getEnabled feature, and just used enabled="true" in the XML
        returnedVal = True
    End Sub
    'Callback for galCalendar getLabel
    Sub GetLabelDay(control As IRibbonControl, ByRef returnedVal)
        returnedVal = "Day"
    End Sub
    'Callback for galCalendar getItemCount
    Sub GetItemCount(control As IRibbonControl, ByRef returnedVal)
        'I use 49 because that's what all the normal date calendars use. After the top row is used for the name of the 7 days, 42 remains.
        'So 42 is basically 7 days * 6 rows
        'The first row will be used to display week days Monday to Sunday
        'The next 6 rows will be used to display the dates depending on where the first date for the corresponding month starts from
        'For example, if we selected a non-leap year february, with the first day starting on a Monday, we would end up using only 4 rows
        'and the remaining 1 row will be entirely blank
        'Similarly, there will be months which start on a Sunday, which is the last column of our calendar control,
        'effectively using at 1 or 2 columns of the last row, depending on whether there are 30 or 31 days for that month
        returnedVal = 49
    End Sub
    
    'Callback for galCalendar getItemLabel
    Sub GetItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
        'So this is where all the action happens (well, at least most of it)
        'So first of all, we need the top row to have the names of the week
        'Of course it is up to the developer to decide on any algorithm to come up with the names of the weekdays
        'Based on feedback from some non-English office users,
        'I've decided to use the WeekDayName function to return the correct Weekday name depending on which day their week starts
        'We now need to identify the weekday on which the first day of the month starts
        lngStartDay = Weekday(DateSerial(CInt(lngSelectedYear), CInt(lngSelectedMonth), 1), mcbytDayOfWeek) 'Year(Date), Month(Date), 1))
        'We also need to know how many days there are in that month
        lngEndDay = Day(DateSerial(CInt(lngSelectedYear), CInt(lngSelectedMonth) + 1, 0)) 'DateSerial(Year(Date), Month(Date) + 1, 0))
        'Now we use a select case to distinquish between the top row of our calendar, and the remaining rows
        'As you know, we have 7 columns. But what we need to be aware of is that the index parameter passed by this function starts from zero (0)
        'So in my select case, I used < 7 instead of <= 7
        Select Case index < 7
            Case True 'Of course you know what this means
                'Here we just pass back the name of each of the 7 weekdays as labels (yeah that's right, labels. Isn't that what the function name suggests?).
                'In other words, you can consider labels as a caption (you know, like for a commandbutton, or a userform. OK, you got the idea)
                returnedVal = Left(WeekdayName(index + 1, True, mcbytDayOfWeek), 2) 'This is where we pass the name of the weekday as a label for the control
            Case Else 'Now, here's where the date part begins (because this is after the first 7 controls (or the top most row of our calendar)
                'You remember we had already captured the weekday on which the first day of the month starts
                'We also know how many days there are in the month
                'Now we need to keep track of how many controls we are iterating through. For that I simply use a variable and increment it
                'Note that the variables I am using have module scope (to know about scope of variables, visit http://support.microsoft.com/kb/141693)
                lngDaySlotCount = lngDaySlotCount + 1
                'So now we need to know when to start passing the days as labels
                'For that I'm also using another module variable to increment the days and check if the days haven't exceeded the maximum days in that month
                If lngDaySlotCount >= lngStartDay And lngDayCount < lngEndDay Then
                    lngDayCount = lngDayCount + 1 'This is the day increment variable
                    lngMonthDays(index) = lngDayCount 'This is an array of 49 items (0 to 48) where I keep track of the current months days. Will explain why I used this where I am using this
                    returnedVal = lngDayCount 'This is where we pass the day as a label for the control
                End If
        End Select
        
    End Sub
    
    'Callback for galCalendar onAction
    Sub galleryOnAction(control As IRibbonControl, id As String, index As Integer)
        'This is where we pass the value of the selected date, on to the sheet
        'Of course this will only pass value to the active cell. So if you've selected a range of cells, still the value will only be passed to the active cell
        'Using as On Error Resume Next statement just to ensure we don't loose the ribbon control due to unwanted errors (for example, if no workbook is active, then there wouldn't be an active cell, would there?)
        On Error Resume Next
        'Now, in the GetItemLabel callback that I used above, I am using an array that I use as a container for the labels of the 42 (49 - 7 top row) button items
        'I had mentioned that I'll explain it's usage later. Well, this is where I am using it.
        'To pass the value of the selected date, I am using the DateSerial function.
        'Now, we already know (or we will know) the selected year and the selected month
        'But that is not enough to pass the date. Yes, we need the day also. But we only know the index of the control that we pressed on
        'But since we have the values of the 42 items in the array, exactly in order of placement on the ribbon, we just need to refer to the value using the index we get as argument to this function
        'Oh and since some of the slots in the first row and the last row may be empty, we just need to check that before we actually pass the value
        If lngMonthDays(index) Then
            'So if lngMonthDays(index) is not zero, then it means it's a valid date for the selected month and year
            'And we pass that to the active cell
            ActiveCell.Value = DateSerial(CInt(lngSelectedYear), CInt(lngSelectedMonth), CInt(lngMonthDays(index)))
        Else
            'If lngMonthDays(index) is zero, then we just assume that the user clicked on the item by mistake and we just clear the activecell value
            'Of course we could just not do anything at all. But I thought what the heck, let the user have an extra reason to not be casual
            'If you want to be more empathetic (or sympathetic), just remove the line below)
            ActiveCell.ClearContents
        End If
    End Sub
    
    'Callback for galYear getItemLabel
    Sub GetItemLabelYear(control As IRibbonControl, index As Integer, ByRef returnedVal)
        'I probably am one of the most laziest person when it comes to programming.
        'So I just hard coded the year selection option to a window of mclngYearWindowFromSelectedYear * 2 years (ie, mclngYearWindowFromSelectedYear before and after current year)
        'And since index starts from 0, you can make out what the following line passes as returnedVal
        returnedVal = Year(Date) - mclngYearWindowFromSelectedYear + index
    End Sub
    
    'Callback for galYear getItemCount
    Sub GetItemCountYear(control As IRibbonControl, ByRef returnedVal)
        'OK, so this is where I tell the ribbon that it should only have X * 2 items in the year selection gallery
        returnedVal = mclngYearWindowFromSelectedYear * 2
    End Sub
    'Callback for galYear getLabel
    Sub GetLabelYear(control As IRibbonControl, ByRef returnedVal)
        'So whenever we need to pass a label (caption) to the year gallery, this is the function we use
        'You will remember that we are passing the current year to lngSelectedYear when the ribbon is loaded
        'But you'll also notice in one of the functions below that we are passing the user selected year also to this variable
        'That's where we keep the label dynamic (look at galleryOnActionYear function)
        returnedVal = lngSelectedYear
    End Sub
    
    'Callback for btnMonthDecrement onAction
    Sub MonthDecrement(control As IRibbonControl)
        If lngSelectedMonth = 1 Then
            lngSelectedMonth = 13
            lngSelectedYear = lngSelectedYear - 1
        End If
        lngSelectedMonth = lngSelectedMonth - 1
        YearMonthChange
    End Sub
    
    'Callback for galMonth getLabel
    Sub GetLabelMonth(control As IRibbonControl, ByRef returnedVal)
        'So everybody who knows the MonthName fuction will know what this does. Those who don't just hit F1
        returnedVal = MonthName(CLng(lngSelectedMonth), True)
    End Sub
    
    'Callback for btnMonthIncrement onAction
    Sub MonthIncrement(control As IRibbonControl)
    
        If lngSelectedMonth = 12 Then
            lngSelectedMonth = 0
            lngSelectedYear = lngSelectedYear + 1
        End If
        lngSelectedMonth = lngSelectedMonth + 1
        YearMonthChange
        
    End Sub
    
    
    'Callback for galMonth getItemLabel
    Sub GetItemLabelMonth(control As IRibbonControl, index As Integer, ByRef returnedVal)
        'You should be able to figure this one out by now. It follows the same principles as the GetItemLabelYear callback function
        returnedVal = MonthName(index + 1, True)
    End Sub
    
    'Callback for galMonth getItemCount
    Sub GetItemCountMonth(control As IRibbonControl, ByRef returnedVal)
        'Same logic as above
        returnedVal = 12
    End Sub
    
    'Callback for galMonth onAction
    Sub galleryOnActionMonth(control As IRibbonControl, id As String, index As Integer)
        'Index starts from 0. So if the user clicks to first item, we are supposed to get 1, not zero, so index + 1
        lngSelectedMonth = index + 1
        'Same logic as above
        YearMonthChange
    End Sub
    
    'Callback for btnYearBigDecrement onAction
    Sub YearBigDecrement(control As IRibbonControl)
        'This will decrement the selected year by X (as defined by the constant mclngBigYearIncrementDecrement
        lngSelectedYear = lngSelectedYear - mclngBigYearIncrementDecrement
        'Calling the invalidation and clean-up routine whenever a year/month changes
        YearMonthChange
    End Sub
    
    'Callback for btnYearSmallDecrement onAction
    Sub YearSmallDecrement(control As IRibbonControl)
    'This will decrement the selected year by 1
        lngSelectedYear = lngSelectedYear - 1
        'Calling the invalidation and clean-up routine whenever a year/month changes
        YearMonthChange
    End Sub
    
    'Callback for galYear onAction
    Sub galleryOnActionYear(control As IRibbonControl, id As String, index As Integer)
        'So here's where we convert the index value we receive when this function is invoked, in to the year which the user intended to select
        'Don't mind the word 'invoked'. It just meant 'called by the user by clicking on any of the mclngYearWindowFromSelectedYear * 2 years'
        lngSelectedYear = Year(Date) - mclngYearWindowFromSelectedYear + index
        'Here we do some cleaning and invalidation. Invalidating a control (or a ribbon) is like using the '.Dirty' function of a range object
        'It's like asking the control to validate itself again, cause we told it to do so ;)
        Call YearMonthChange
    End Sub
    
    'Callback for btnYearSmallIncrement onAction
    Sub YearSmallIncrement(control As IRibbonControl)
        'This will increment the selected year by 1
        lngSelectedYear = lngSelectedYear + 1
        'Calling the invalidation and clean-up routine whenever a year/month changes
        YearMonthChange
    End Sub
    
    'Callback for btnYearBigIncrement onAction
    Sub YearBigIncrement(control As IRibbonControl)
        'This will increment the selected year by X (as defined by the constant mclngBigYearIncrementDecrement
        lngSelectedYear = lngSelectedYear + mclngBigYearIncrementDecrement
        'Calling the invalidation and clean-up routine whenever a year/month changes
        YearMonthChange
    End Sub
    Sub YearMonthChange()
        
        'So we are invalidating the three controls (1)Day, (2)Year, (3)Month
        miruCalendar.InvalidateControl "galCalendar"
        miruCalendar.InvalidateControl "galYear"
        miruCalendar.InvalidateControl "galMonth"
        'We are also resetting our Day and DaySlot counters to empty
        lngDayCount = Empty
        lngDaySlotCount = Empty
        'And since each month is different from each, we cannot hold the container constant
        'It's an array, and we need to clear it using the Erase function
        Erase lngMonthDays
        
    End Sub
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Test copy Activate Ribbon Tab In Excel 2007 2010 *
    By Excel Fox in forum Test Area
    Replies: 16
    Last Post: 01-22-2019, 05:05 PM
  2. Excel 2003 Classic Menu in Excel 2007-2010
    By Excel Fox in forum Classic Menu
    Replies: 7
    Last Post: 09-10-2014, 10:29 PM
  3. Ribbon Calendar DatePicker Word 2007-2010
    By Excel Fox in forum Download Center
    Replies: 24
    Last Post: 09-23-2013, 09:07 AM
  4. Replies: 1
    Last Post: 02-14-2013, 11:08 AM
  5. Add ribbon programmatically to Excel 2010 using VBA
    By heapifyman in forum Excel Ribbon and Add-Ins
    Replies: 6
    Last Post: 07-18-2011, 09:16 PM

Tags for this Thread

Posting Permissions

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