none
How to add programmatically events to a MonthView control RRS feed

  • Question

  • Hi All

    Due to instability problems I am checking usability, and if needed, I am adding a reference to the mscomct2.ocx to my Excel workbook at run time.

    Then programmatically I am adding a MonthView control to an existing UserForm per code below.

    Public Sub AddMonthPicker()
    .
    .
    .
        Set MonthView1 = CalendarForm.Controls.Add("MSComCtl2.MonthView", "MonthView", True)
        With MonthView1
            .Appearance = 0 'ccFlat
            .BorderStyle = 1 'ccFixedSingle
            .ShowWeekNumbers = True
            .BackColor = &HBA5A03
            .TitleBackColor = &HFFA782
            .TitleForeColor = &HBA5A03
            .BorderStyle = cc2None
            .Value = Date
            .StartOfWeek = 2 'mvwSunday
        End With
    .
    .
    .
    End Sub

    Everything work fine but I was not able to add events to the MonthView like:

    Private Sub MonthView_DateDblClick(ByVal DateDblClicked As Date)
        ActiveCell.Value = MonthView.Value
        Me.Hide
    End Sub

    Thanks in advance for any help on this issue,

    Rotem



    Sunday, May 27, 2012 11:05 PM

Answers

  • Following work on other projects i stumbled on this old issue again and decided to solve this problem.  In the link below one may find an indirect solution to the question in this thread.

    It will not show how to add events but it will allow to safely use the MonthView control.

    http://code.msdn.microsoft.com/How-to-use-MonthView-5e8b32be

    Rotem

    • Marked as answer by RotemQ Saturday, November 3, 2012 10:28 PM
    • Edited by RotemQ Sunday, November 4, 2012 7:03 AM
    Saturday, November 3, 2012 10:27 PM

All replies

  • After much research I am able to add the code for a control to an EXISTING form but I cannot get the code to work.

    However, I have been successful in adding a new form and control together with the code and it works. Maybe it will help and you can get some further information on how to achieve this in an existing form.

    There is a bug in that if the form is created and renamed and then the form is removed; if the code is run again then it cannot rename the form to the same name as the form that was deleted. However, if the workbook is saved and closed and re-opened after deleing the form then it works again. The code tests for the existance of CalendarForm before creating a new one so maybe this will not be a problem.

    The example code was tested in a standard module as well as in Workbook Open event.

    I will be interested in how you go with it.

    Following code has been modified since original post:

    Sub AddCalendar()
        'Dim ctrl As Control    'Can't use control until after a form created
        Dim ctrl As Object
        Dim objCalendarFrm As VBComponent
        'Dim mthView As MSForms.Control 'Can't use control until after a form created
        Dim mthView As Object
        Dim strCtrlName As String
        Dim X As Long
        Dim bolCodeExists As Boolean
        Dim i As Long
       
        strCtrlName = "MonthView1"
        'Test if Userform exists before attempting to create.
        On Error Resume Next
        Set objCalendarFrm = ThisWorkbook.VBProject.VBComponents("CalendarForm")
        On Error GoTo 0         'Resume error trapping ASAP
       
        If Not objCalendarFrm Is Nothing Then       'Not Nothing so CalendarForm exists
            'MsgBox objCalendarFrm.Name & " exists."  'Used during testing
       
            'Test for existing MonthView control on CalendarForm
            'Must use the variable in lieu of CalendarForm because _
             code will not compile if CalendarForm does not exist.
            'See http://support.microsoft.com/kb/157609 for more info on following line
            For Each ctrl In VBA.UserForms.Add(objCalendarFrm.Name).Controls
                If TypeName(ctrl) = "MonthView" Then
                    'MsgBox TypeName(ctrl) & " control exists." & vbCrLf _
                            & "Skipping form and Control creation."     'Used during testing
                    GoTo AfterFormAndControlCreate    'Calendar and MonthView control exist.
                End If
            Next ctrl
        End If
                          
        If objCalendarFrm Is Nothing Then   'Userform does not exist so create
            'MsgBox "CalendarForm does NOT exist."  'Used during testing
            'Create the User Form
            Set objCalendarFrm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
           
            With objCalendarFrm
                .Properties("Height") = 300
                .Properties("Width") = 300
                .Properties("Caption") = "Select Date"
                On Error Resume Next
                'Following line errors if CalendarForm is deleted and _
                 code is re-run before closing and re-opening the workbook _
                 and hense the On Error Resume Next.
                .Name = "CalendarForm"
                On Error GoTo 0
            End With
         End If
        
        'Note code skips this to AfterFormAndControlCreate: label _
         if MonthView exists on CalendarForm.
        'Create the MonthView control on Userform
       
        Set mthView = objCalendarFrm.Designer.Controls _
                        .Add("MSComCtl2.MonthView", strCtrlName, True)
        With mthView
            .Left = 50
            .Top = 50
            .Height = 115
            .Width = 145
            .Appearance = 0 'ccFlat
            .BorderStyle = 1 'ccFixedSingle
            .ShowWeekNumbers = True
            .BackColor = &HBA5A03
            .TitleBackColor = &HFFA782
            .TitleForeColor = &HBA5A03
            .BorderStyle = 0  'cc2None on avaiailable after reference
            .Value = Date
            .StartOfWeek = 2 'mvwSunday
        End With
        On Error GoTo 0
       
        'Create the Event code for the MonthView control
       
        With objCalendarFrm.CodeModule
             X = .CountOfLines
             bolCodeExists = False
             For i = 1 To X
                If .Lines(i, 1) = "Private Sub " & strCtrlName & _
                                    "_DateDblClick(ByVal DateDblClicked As Date)" Then
                    bolCodeExists = True
                    Exit For
                End If
            Next i
           
            If bolCodeExists = False Then
                .InsertLines X + 1, "Private Sub " & strCtrlName & _
                                    "_DateDblClick(ByVal DateDblClicked As Date)"
                .InsertLines X + 2, "ActiveCell.Value = DateDblClicked"
                .InsertLines X + 4, "End Sub"
            End If
        End With
           
    AfterFormAndControlCreate:
       
        'Must use the variable in lieu of CalendarForm because _
         code will not compile if CalendarForm does not initially exist.
        'See http://support.microsoft.com/kb/157609 for more info on following line
        VBA.UserForms.Add(objCalendarFrm.Name).Show vbModal   'Not essential to run from here
           
        'If Modal form shown then following code does not run until after Userform is closed.
        Set mthView = Nothing
        Set objCalendarFrm = Nothing
    End Sub

    Following added with Edit:

    I forgot include that in the VBA editor you need to open Tools - > References and then scroll down to Microsoft Visual Basic For Applications Extensibility 5.3 and check the box. (Ensure you check the box; not just select the line.)


    Regards, OssieMac




    • Edited by OssieMac Thursday, May 31, 2012 1:12 AM
    Monday, May 28, 2012 12:49 PM
  • Try something like this, with code in the three modules as indicated

    ''' normal module
    Sub auto_open()
    Dim objRef As Object
         With ThisWorkbook.VBProject.References
                 On Error Resume Next
                 Set objRef = .Item("MSComCtl2")
                 On Error GoTo 0
                 If objRef Is Nothing Then
    Set objRef = .AddFromGuid("{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}", 0, 0)
                 ' ThisWorkbook.Save    '
                 End If
         End With
    End Sub
    
    ''' class module named Class1
    Public WithEvents mv As MonthView
    Private Sub mv_DateDblClick(ByVal DateDblClicked As Date)
    Dim obj As Control
         Set obj = mv
         obj.Parent.Caption = obj.Value
    End Sub
    
    '''userform
    Private c As Class1
    Private Sub UserForm_Initialize()
         AddMonthPicker
    End Sub
    
    Public Sub AddMonthPicker()
         Set c = New Class1
         Set c.mv = Me.Controls.Add("MSComCtl2.MonthView", "MonthView", True)
         With c.mv
                 .Appearance = 0        'ccFlat
                 .BorderStyle = 1        'ccFixedSingle
                 .ShowWeekNumbers = True
                 .BackColor = &HBA5A03
                 .TitleBackColor = &HFFA782
                 .TitleForeColor = &HBA5A03
                 .BorderStyle = cc2None
                 .Value = Date
                 .StartOfWeek = 2        'mvwSunday
         End With
    End Sub

    double-click on a date and look at the caption

    Ideally set a flag somewhere after adding the reference that saves with the file, eg in a hidden name, doc properties, hidden cell, registry. It'd be quicker to check the flag than the project references.

    Peter Thornton

    Monday, May 28, 2012 7:25 PM
    Moderator
  • If the control doesn't expose a DoubleClick event then you can't add one yourself.

    You can however use the control's Click event as follows;

    Private Sub MonthView_Click()
        Static click1 As Single
        
        If click1 = 0 Then
            click1 = Timer
        Else
            If Timer - click1 < 0.5 Then
                'Run DoubleClick code...
                click1 = 0
            Else
                click1 = Timer
            End If
        End If
    End Sub

    Vary the timing to suit your needs but this should emulate a DoubleClick event.

    • Proposed as answer by Ray Brack Wednesday, May 30, 2012 10:10 PM
    • Unproposed as answer by Ray Brack Thursday, May 31, 2012 1:29 AM
    Tuesday, May 29, 2012 6:17 AM
  • @ Rotem,

    Have you had any success with your problem to date?

    @ Ray Brack,

    I am interested to know why you believe you have answered the OP's question. The question was:

    I was not able to add events to the MonthView like

    Private Sub MonthView_DateDblClick(ByVal DateDblClicked As Date)
        ActiveCell
    .Value = MonthView.Value
       
    Me.Hide
    End Sub

    Firstly the Double Click event is a standard event for the Control.

    Because the OP was using code to add the control I feel sure that he wants to add the code with VBA similar to what I have provided (and modified a couple of times since original post.) The code works if inserted into a standard module of a new workbook and set the reference " Microsoft Visual Basic For Applications Extensibility 5.3" and run the code.

    What my code will not do is create the control on a existing Userform and it will not rename a userform to a previously used name for a userform that has been deleted until after saving and closing and reopening the workbook.


    Regards, OssieMac

    Thursday, May 31, 2012 12:54 AM
  • Hi OssieMac,

    Misunderstood the question, thought he was trying to add a DoubleClick event for a control that didn't support it.

    Cheers,

    Ray

    Thursday, May 31, 2012 1:30 AM
  • Following work on other projects i stumbled on this old issue again and decided to solve this problem.  In the link below one may find an indirect solution to the question in this thread.

    It will not show how to add events but it will allow to safely use the MonthView control.

    http://code.msdn.microsoft.com/How-to-use-MonthView-5e8b32be

    Rotem

    • Marked as answer by RotemQ Saturday, November 3, 2012 10:28 PM
    • Edited by RotemQ Sunday, November 4, 2012 7:03 AM
    Saturday, November 3, 2012 10:27 PM
  • Your link is essentially the same as I suggested for you in this thread (also showed how to programmatically add the Reference) I take it you didn't try it. Both examples show how to use WithEvents to trap events of the added at runtime, without the need to add new code to the Project.

    Peter Thornton

    Monday, November 5, 2012 8:32 AM
    Moderator
  • Hi Peter

    Thanks for U input, it helped me to notice that I did have a mistake with the attached code (fixed).
    As U may see now, my recommended solution is somewhat more holistic.
    Unfortunately only dealing with events will not help users to safely use the MonteView control.

    Rotem

    Tuesday, November 6, 2012 2:29 PM
  • Unfortunately only dealing with events will not help users to safely use the MonteView control.

    Afraid I don't follow, in what way do either of the examples (as posted in this thread and your link) not help users to use the control "safely", and by implication what do you mean by "not safe". (Both examples covered more than simply events)

    Peter Thornton

    Tuesday, November 6, 2012 3:35 PM
    Moderator
  • Well what I meant by ‘safely’ was that if we have a file (e.g. Excel) that is “using” MonthView control we like it to work on any occasion regardless on the OS type (win) in use, the Office version, whether mscomct2.ocx was installed on the user PC, and if it was, regardless of whether it was installed in the correct folder (sometimes, from my experience, we can fine this ocx installed in more than one location). Moreover if it is a shared file we want it to run flawless on all users PCs (each of them may have different ocx “status”).

    If however mscomct2.ocx is not present we want to prevent from the user to see compilation errors, etc.

    Hope that clarify my intention

    R

    Tuesday, November 6, 2012 4:16 PM