18

I am looking for a control to add to userForm to enable a user to pick the date. I found a good one at this address

Formatting MM/DD/YYYY dates in textbox in VBA

from Siddharth Rout which is perfect but it generates invisible sheets which is not necessary for my application. I tried to stop creating sheets but I couldn't figure it out.

Can you please let me know how I can properly modify the Siddharth Rout code or point me to another application like that?

Thanks for your time

Joe
  • 8,251
  • 3
  • 18
  • 23
user1760110
  • 2,256
  • 5
  • 29
  • 37
  • It doesn't appear to generate sheets - it simply adds a VBA code module which you need. – brettdj Nov 16 '12 at 02:56
  • possible duplicate of [Formatting MM/DD/YYYY dates in textbox in VBA](http://stackoverflow.com/questions/12012206/formatting-mm-dd-yyyy-dates-in-textbox-in-vba) – brettdj Nov 16 '12 at 02:56

5 Answers5

23

OFFICE 2013 INSTRUCTIONS:

(For Windows 7 (x64) | MS Office 32-Bit)

Option 1 | Check if ability already exists | 2 minutes

  1. Open VB Editor
  2. Tools -> Additional Controls
  3. Select "Microsoft Monthview Control 6.0 (SP6)" (if applicable)
  4. Use 'DatePicker' control for VBA Userform

Option 2 | The "Monthview" Control doesn't currently exist | 5 minutes

  1. Close Excel
  2. Download MSCOMCT2.cab (it's a cabinet file which extracts into two useful files)
  3. Extract Both Files | the .inf file and the .ocx file
  4. Install | right-click the .inf file | hit "Install"
  5. Move .ocx file | Move from "C:\Windows\system32" to "C:\Windows\sysWOW64"
  6. Run CMD | Start Menu -> Search -> "CMD.exe" | right-click the icon | Select "Run as administrator"
  7. Register Active-X File | Type "regsvr32 c:\windows\sysWOW64\MSCOMCT2.ocx"
  8. Open Excel | Open VB Editor
  9. Activate Control | Tools->References | Select "Microsoft Windows Common Controls 2-6.0 (SP6)"
  10. Userform Controls | Select any userform in VB project | Tools->Additional Controls
  11. Select "Microsoft Monthview Control 6.0 (SP6)"
  12. Use 'DatePicker' control for VBA UserForm

Okay, either of these two steps should work for you if you have Office 2013 (32-Bit) on Windows 7 (x64). Some of the steps may be different if you have a different combo of Windows 7 & Office 2013.

The "Monthview" control will be your fully fleshed out 'DatePicker'. It comes equipped with its own properties and image. It works very well. Good luck.

Site: "bonCodigo" from above (this is an updated extension of his work)
Site: "AMM" from above (this is just an exension of his addition)
Site: Various Microsoft Support webpages

Elias
  • 723
  • 3
  • 11
  • 18
  • 6
    It's worth noting that the 32-bit version of Office is **required** for these steps to work. Since the *mscomct2.ocx* ActiveX control is a 32-bit control it will not work with the 64-bit version of office. Also, in case someone else is as desperate as I was: The [Microsoft Outlook Date Control](http://msdn.microsoft.com/en-us/library/office/ff868818%28v=office.15%29.aspx) cannot be used on a Visual Basic **UserForm**. – IInspectable Oct 27 '14 at 18:05
  • I followed these steps and still cannot see the control in the toolbox. I even manually browsed for the mscomct2.ocx file and manually added it to the references and yet I still could not see it... – user32882 Aug 16 '15 at 22:52
  • The link to the cab file in this solution is currently broken. – Dawood ibn Kareem Feb 19 '20 at 22:13
  • @DawoodibnKareem The .cab file can be obtained using [Internet Archive](http://www.archive.org): 1.) [archived link to Microsoft article about the .cab file](https://web.archive.org/web/20120808064855/http://support.microsoft.com/kb/297381) 2.) [The .cab file from the archive.org](https://web.archive.org/web/20120113035142/http://activex.microsoft.com/controls/vb6/MSComCt2.cab) – Jaroslav Svestka Oct 08 '20 at 13:53
18

You could try the "Microsoft Date and Time Picker Control". To use it, in the Toolbox, you right-click and choose "Additional Controls...". Then you check "Microsoft Date and Time Picker Control 6.0" and OK. You will have a new control in the Toolbox to do what you need.

I just found some printscreen of this on : http://www.logicwurks.com/CodeExamplePages/EDatePickerControl.html Forget the procedures, just check the printscreens.

Matt Roy
  • 1,455
  • 1
  • 17
  • 27
3

Just throw some light in to some issues related to this control.

Date picker is not a standard control that comes with office package. So developers encountered issues like missing date picker controls when application deployed in some other machiens/versions of office. In order to use it you have to activate the reference to the .dll, .ocx file that contains it.

In the event of a missing date picker, you have to replace MSCOMCT2.OCX file in System or System32 directory and register it properly. Try this link to do the proper replacement of the file.

In the VBA editor menu bar-> select tools-> references and then find the date picker reference and check it.

If you need the file, download MSCOMCT2.OCX from here.

Community
  • 1
  • 1
bonCodigo
  • 14,268
  • 1
  • 48
  • 91
  • 1
    the file u mentioned is not available now – Maddy8381 Jan 20 '20 at 06:59
  • 1
    @Maddy8381 A .cab file (like .zip) with the .ocx can be obtained using [Internet Archive](http://www.archive.org): 1.) [link to Microsoft using archive.org](https://web.archive.org/web/20120808064855/http://support.microsoft.com/kb/297381) 2.) [The .cab file from the archive.org](https://web.archive.org/web/20120113035142/http://activex.microsoft.com/controls/vb6/MSComCt2.cab) – Jaroslav Svestka Oct 08 '20 at 13:51
3

In Access 2013. Drop a "Text Box" control onto your form. On the Property Sheet for the control under the Format tab find the Format property. Set this to one of the date format options. Job's done.

FW55
  • 31
  • 1
1

A calendar form can be used as an alternative to the date picker. On a userform example, I used this calendar form to add dates to text boxes. When double-clicked on the textbox, the calendar form is displayed :

enter image description here

Private Sub txtDOTDate_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Call GetCalendar
End Sub

Sub GetCalendar()
    dateVariable = CalendarForm.GetDate(DateFontSize:=11, _
        BackgroundColor:=RGB(242, 248, 238), _
        HeaderColor:=RGB(84, 130, 53), _
        HeaderFontColor:=RGB(255, 255, 255), _
        SubHeaderColor:=RGB(226, 239, 218), _
        SubHeaderFontColor:=RGB(55, 86, 35), _
        DateColor:=RGB(242, 248, 238), _
        DateFontColor:=RGB(55, 86, 35), _
        TrailingMonthFontColor:=RGB(106, 163, 67), _
        DateHoverColor:=RGB(198, 224, 180), _
        DateSelectedColor:=RGB(169, 208, 142), _
        TodayFontColor:=RGB(255, 0, 0))
If dateVariable <> 0 Then frmflightstats.txtDOTDate = dateVariable
End Sub

Source of template

kadrleyn
  • 364
  • 1
  • 5