Search code examples
excelvbadatecalendaruserform

How to incorporate popup calendarform into an existing Userform

I'm very impressed by Trevor Eyre's version of the pop up calendar (answer # 11) and was excited to incorporate into a Userform that I am developing, which is challenging as I have VERY basic experience with VBA.

Formatting MM/DD/YYYY dates in textbox in VBA (Answer # 11) Link to the example spreadsheet of Trevors https://www.dropbox.com/sh/0n9gyc32rlr31n2/AAAW7ADAWiLEZRHftERro9Tva?dl=0

For example, I have a userform named SECINC and have a text box (IncidentDate) where the user would have had to enter the date of the incident, until I came across Trevor's version of the pop up calendar. I am on Excel 2013 (64 bit) and the MS date picker is not available.

I have copied over all the code from the module1 into module1 of my project book and have saved and imported the CalendarForm and then I have my own userform, Refer to link:
Example of SECINC userform

I am happy to use just the basic CalendarForm as is, but the one thing I am missing is how do I call up the Calendar form when the user clicks the IncidentDate textbox and the selected date populates that IncidentDate box.

I have added an 'image' of a calendar and wondering how I link it to the CalendarForm so that when it is clicked it will open the Calendar and drop the date into the IncidentDate textbox.

I would need the date format in the IncidentDate textbox to display in DD/MM/YY format.

I was thinking that perhaps it would be advantage for us who are visually based, to see it in the form of a video tutorial and for those of use who are new to VBA.

If I have confused you, or you require further information, please advise.

Thanking you in advance,

TheShyButterfly

Solution

  • You need a click event on your image, that will call the calendar and fill in the date field

    Right click on the calendar image and choose "view code" to create the click event sub. Then add the command below as specified on Trevor's answer:

    Private Sub Image1_Click()
    your_date_field_name.Value = CalendarForm.GetDate
End Sub

    Try to be a bit less "literal" next time you ask a question, we don't need that much details for such a simple question, it's confusing. I suggest you to read this