0

I am working on my first VBA project and I am really stuck.

On my Excel sheet I need a Date Picker. The Excel version I am using does not have one, so I created my own calendar using the code from the link below ( The one created by L42, with the 42 labels and so on):

Formatting MM/DD/YYYY dates in textbox in VBA

The calendar looks just like the one in the image, so all good so far.

enter image description here

On my Excel spreadsheet I added a code which makes the calendar pop up only in two cells, E2 and F2, which represent the start date and the end date for my data.

Private Sub worksheet_selectionchange(ByVal target As Range)
If Not Application.Intersect(Range("E2"), target) Is Nothing Then
MyCalendar.Show
End If
If Not Application.Intersect(Range("F2"), target) Is Nothing Then
MyCalendar.Show
End If
End Sub

Everything works fine, the calendar only pops up when I click on these two cells only. So, when I click on E2, the calendar pops up, I select a date, then a message box with the selected date pops up, I click ok to close it, then I close the calendar, but the date I clicked on will not be inserted in my E2 cell.

I guess I need a code in the select_label procedure below which will make the date I click on in the calendar and pops up in the MsgBox actually fill my excel E2 or F2 cells. 

    Private Sub select_label(msForm_C As MSForms.Control)
        '/* Capture the selected date */
        Dim i As Integer, sel_date As Date
        i = Split(msForm_C.Name, "_")(1) - 1
        sel_date = FirstCalSun(curMonth) + i

        '/* Transfer the date where you want it to go */
        MsgBox sel_date

End Sub

Any help is much appreciated.

Thank you.

Ela
  • 11
  • 4
  • what is `Cell`? Make sure you use `Option Explicit`: In the VBA editor go to *Tools* › *Options* › *[Require Variable Declaration](https://www.excel-easy.com/vba/examples/option-explicit.html)*. – Pᴇʜ Jan 25 '19 at 07:56
  • @Peh: Cell refers to the cell in my excel spreadsheet where my calendar pops up when I click on it. – Ela Jan 25 '19 at 08:15
  • No it does not refer to anything yet. At least you didn't show that reference in your code. Do you have `Option Explicit` activated? – Pᴇʜ Jan 25 '19 at 09:24
  • You are right, it should refer to cells E2 and F2 from my workbook.Yes, I have it activated. – Ela Jan 25 '19 at 09:35
  • If it is activated then `Cell.Value` should throw an error. Have a look here [Calling a userform and returning a value](https://stackoverflow.com/questions/18966137/calling-a-userform-and-returning-a-value). That technique should solve your issue. – Pᴇʜ Jan 25 '19 at 09:36
  • Possible duplicate of [Calling a userform and returning a value](https://stackoverflow.com/questions/18966137/calling-a-userform-and-returning-a-value) – Pᴇʜ Jan 25 '19 at 09:37

0 Answers0