Excel leave existing date in cell (calendar VBA)

mrwad

New Member
Joined
Oct 16, 2018
Messages
47
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:
Code:
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,172
What about

Code:
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
 
Last edited:

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

You could test following

Code:
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

New Member
Joined
Oct 16, 2018
Messages
47
What about

Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,675
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,082
Messages
5,466,535
Members
406,484
Latest member
kaksolver

This Week's Hot Topics

Top