Excel leave existing date in cell (calendar VBA)

[mrwad]

I am using VBA calendar to input dates in my workbook. I have added code to my worksheet to call calendar on double click of cell. So basically when I double click cell in range I get calendar form, where I pick up the date and then it is inserted in double clicked cell. The problem is if I already have some date in cell and double click it and then just close calendar instead of picking up some date it inserts 1.1.1990 or 00:00:00. How I can make to leave existing date if I just press close?

I have used this calendar:
https://trevoreyre.com/portfolio/excel-datepicker/


Code to call calendar form by double clicking cell in range:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


  Dim xRg As Object


    If Not Intersect(Target, Range("E7:F187")) Is Nothing Then dateVariable = CalendarForm.GetDate
   
  For Each xRg In Selection.Cells
  xRg.Value = dateVariable
  Next xRg


   
End Sub

 

[Kamolga]

What about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


  Dim xRg As Object


    If Not Intersect(Target, Range("E7:F187")) Is Nothing Then dateVariable = CalendarForm.GetDate
  if datevariable=0 then
   exit sub
  else
  For Each xRg In Selection.Cells
  xRg.Value = dateVariable
  Next xRg
End if
'datevariable=0


    [COLOR=#333333]End Sub[/COLOR]

If it does not work with =0, you can try with cancel, nothing or close

 

[James006]

Hello,

You could test following

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  With Target
        If .Cells.Count > 1 Then Exit Sub
        If Intersect(.Cells, Me.Range("E7:F187")) Is Nothing Then Exit Sub
        If .Value > 0 Then Exit Sub
        .Value = CalendarForm.GetDate
        .NumberFormat = "dd-mmm-yy"
        Cancel = True
  End With
End Sub

Hope this will help

 

[mrwad]

What about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


  Dim xRg As Object


    If Not Intersect(Target, Range("E7:F187")) Is Nothing Then dateVariable = CalendarForm.GetDate
  if datevariable=0 then
   exit sub
  else
  For Each xRg In Selection.Cells
  xRg.Value = dateVariable
  Next xRg
End if
'datevariable=0


    [COLOR=#333333]End Sub[/COLOR]

If it does not work with =0, you can try with cancel, nothing or close

This one works, the only problem is that after closing my calendar form my cell gets selected and I see formula inside my cell. How I can make it close my calendar form without selecting cell after it?

 

[Fluff]

Cross posted https://chandoo.org/forum/threads/excel-leave-excising-date-in-cell-calendar-vba.40280/

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.