• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 91
  • Last Modified:

Date picker

How do i get this code to initiate on a double click on the in the cell. At the moment the date picker comes up immediately the cell is selected. This is a little inconvenient.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     'check cells for desired format to trigger the calendarfrm.show routine
     'otherwise exit the sub
    Dim DateFormats, DF
    DateFormats = Array("d/mmmm/yy;@", "d mmmm yyyy")
    For Each DF In DateFormats
        If DF = Target.NumberFormat Then
            If CalendarFrm.HelpLabel.Caption <> "" Then
                CalendarFrm.Height = 191 + CalendarFrm.HelpLabel.Height
            Else: CalendarFrm.Height = 191
                CalendarFrm.Show
            End If
        End If
    Next
End Sub


thank you
0
Sam Coombes
Asked:
Sam Coombes
  • 7
  • 5
1 Solution
 
Martin LissRetired ProgrammerCommented:
Move the code to the Before_DoubleClick code for the sheet.
0
 
Martin LissRetired ProgrammerCommented:
In other words

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'check cells for desired format to trigger the calendarfrm.show routine
     'otherwise exit the sub
    Dim DateFormats, DF
    DateFormats = Array("d/mmmm/yy;@", "d mmmm yyyy")
    For Each DF In DateFormats
        If DF = Target.NumberFormat Then
            If CalendarFrm.HelpLabel.Caption <> "" Then
                CalendarFrm.Height = 191 + CalendarFrm.HelpLabel.Height
            Else: CalendarFrm.Height = 191
                CalendarFrm.Show
            End If
        End If
    Next
End Sub

Open in new window

1
 
Sam CoombesAuthor Commented:
thats what I thought but I still cant get that to work
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Martin LissRetired ProgrammerCommented:
Can you attach your workbook or a sample workbook?
0
 
Sam CoombesAuthor Commented:
0
 
Martin LissRetired ProgrammerCommented:
OK, you had two problems.
1) The code only works (even in the Worksheet_SelectionChange event) if a field has a date format of either "d/mmmm/yy;@" or "d mmmm yyyy". I couldn't find one that did so I changed cell J7.
2) in Tools->References one of the references was marked as "MISSING" and I corrected that by unchecking it

I then copied the code to the Worksheet_BeforeDoubleClick event and now double-clicking that cell brings up the calendar. Note that there are ways to have the calendar pop up on double-click in that column or any other specific column(s) without them having to have any specific date format. Let me know if that's something you'd like.
28980347.xlsm
1
 
Sam CoombesAuthor Commented:
That sounds great but the file you sent me doesn't do that yet.

Thank You
0
 
Sam CoombesAuthor Commented:
Correction the Calendar function works amazing thank you so much
0
 
Sam CoombesAuthor Commented:
The Date picker still keeps showing False on the sheet
0
 
Sam CoombesAuthor Commented:
Works perfect amazing thank you
0
 
Sam CoombesAuthor Commented:
Thank you very much
0
 
Martin LissRetired ProgrammerCommented:
You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now