Calendar to be Pop up on the Given Range on Double Click

  • I have been trying to pop up the calendar on the Range B4:B2000 like this below code.


    Just looking for a solution that whenever i double click on the whatever range cell then Calendar should be open then i will select a date then that date will be load into the cell which i double i click.


    I have attached a sheet. I was trying to make it correct but its very difficult. Your help will be appreciated.


    Code
    1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    2. If Not Intersect(Target, Range("B4:B2000")) Is Nothing Then
    3. AdvancedCalendar2.Show
    4. Cancel = True
    5. End If
    6. End Sub
  • Hello,


    If you are looking only ... for the event macro ...

    Code
    1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    2. If Target.Column <> 2 Then Exit Sub
    3. Application.Run ("AdvancedCalendar2")
    4. Cancel = True
    5. End Sub

    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi, Carim thank you for the prompt response.


    Yes its working but it just pop-up the calendar but not loading/pasting the date on the cell where double clicked to open the Calendar.

    range Would be B4:B2000

  • Hi, Carim thank you for the prompt response.


    Yes its working but it just pop-up the calendar but not loading/pasting the date on the cell where double clicked to open the Calendar.

    range Would be B4:B2000

    As I said ... based on your question ... just looked at the double-click event ...


    Have not looked into the CalendarForm developed by Trevor Eyre ...


    By the way ... what are the modifications you have added to the original code ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • No, i didn't make any changes just added the Intersect function to popup the calendar.

    So ... what about the Range ("H61") .... and the IF instruction about the DateVariable ... ???


    Right now, do not have the time to take a look at the whole code ...?(... to look for the whole logic ....

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Carim


    Alright no problem. I am not in hurry and i wasn't aware that the whole code would have to review for this change. If such thing is possible then i can wait for the solution if it doesn't bother you.

  • A quick fix ... please make sure the modifications do work as expected ...;)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • The Calendar is not named AdvancedCalendar2


    Code
    1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    2. If Not Intersect(Target, Range("B4:B2000")) Is Nothing Then
    3. CalendarForm.Show
    4. Cancel = True
    5. End If
    6. End Sub
  • Hi, royUK when i use this below function the code pop-ups the Calendar is attached below. I do not know why.


    Code
    1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    2. If Not Intersect(Target, Range("B4:B2000")) Is Nothing Then
    3. CalendarForm.Show
    4. Cancel = True
    5. End If
    6. End Sub




  • Range B4:B2000 by Sheet function.

    So i can add it up to other sheets as well.


    Sheet1.Rage("B4:B2000")

    Sheet3.Range("C25:C500")


    and for other sheets as well.


    I just want to populate the Calendar on the said ranges.

  • I see that AdvancedCalendar2 is a Procedure that formats the Form so try


    Code
    1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    2. If Not Intersect(Target, Range("B4:B2000")) Is Nothing Then
    3. Module1.AdvancedCalendar2
    4. Cancel = True
    5. End If
    6. End Sub

    It does seem to be writing the date to the whole range though


    Code
    1. If dateVariable = 0 Then Range("B4:B2000") = dateVariable
  • Thank you royUK I got your point too. I used your below code its working fine.

    Code
    1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    2. If Not Intersect(Target, Range("B4:B2000")) Is Nothing Then
    3. Module1.AdvancedCalendar2
    4. Cancel = True
    5. End If
    6. End Sub


    But no need for this, because i just want to paste the date via Calendar on the active cell and now its working. Thank you.


    Code
    1. If dateVariable = 0 Then Range("B4:B2000") = dateVariable