none
Calendar function on various Excel versions RRS feed

  • Question

  • Hello! I want to use a calendar function in my User Form on Excel 2010, I have installed ActiveX control - MSCOMCT2.OCX on my 64 bit Windows. However, when I pass my User Form to other clients, it does not work, because simply they either have other Office versions or different operating system, etc.

    Could you please, propose any other robust calendar function that would work on different Office versions no matter what...

    It is very annoying if every time there is need for MSCOMCT2.OCX installation and registration... And there is still high probability that it won't work...

    Thank you and looking forward to hear more about any possibilities. 

    Wednesday, April 15, 2015 1:47 PM

Answers

  • You can download a free calendar built entirely from "native" userform components fromhttp://www.vbaexpress.com/kb/getarticle.php?kb_id=543. If you use it, the calendar will be saved with the workbook, so you won't need to install anything on users' PCs.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, April 15, 2015 4:28 PM
  • Indeed the control may need unpacking to the correct folder, depending if the system is 32/64 bit, and registering. Thereafter it should work with Office-32 even in OS64, but it won't work with Office-64.

    Ideally an installer will do the lot for you, but if you only want to distribute a VBA file you could do a simple test first time to check if installed, if not explain to the user what to do. Eg

    Function MonthViewIsReg() As Boolean
    Dim obj As Object
        On Error GoTo errExit
        Set obj = CreateObject("MSComCtl2.MonthView")
        MonthViewIsOK = Not obj Is Nothing
    errExit:
    End Function

    This might take a second or two so if OK save a flag say to the registry (SaveSetting) to check first in future.

    Remove the reference to MSComCtl2 that gets added when you add the control to your form, you don't need it.

    Compared to other controls the DatePicker and MonthView are relatively straight forward to recreate in pure VBA and there are various examples out there, I might add one myself!


    Wednesday, April 15, 2015 4:42 PM
    Moderator

All replies

  • You can download a free calendar built entirely from "native" userform components fromhttp://www.vbaexpress.com/kb/getarticle.php?kb_id=543. If you use it, the calendar will be saved with the workbook, so you won't need to install anything on users' PCs.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, April 15, 2015 4:28 PM
  • Indeed the control may need unpacking to the correct folder, depending if the system is 32/64 bit, and registering. Thereafter it should work with Office-32 even in OS64, but it won't work with Office-64.

    Ideally an installer will do the lot for you, but if you only want to distribute a VBA file you could do a simple test first time to check if installed, if not explain to the user what to do. Eg

    Function MonthViewIsReg() As Boolean
    Dim obj As Object
        On Error GoTo errExit
        Set obj = CreateObject("MSComCtl2.MonthView")
        MonthViewIsOK = Not obj Is Nothing
    errExit:
    End Function

    This might take a second or two so if OK save a flag say to the registry (SaveSetting) to check first in future.

    Remove the reference to MSComCtl2 that gets added when you add the control to your form, you don't need it.

    Compared to other controls the DatePicker and MonthView are relatively straight forward to recreate in pure VBA and there are various examples out there, I might add one myself!


    Wednesday, April 15, 2015 4:42 PM
    Moderator