How to AutoHide the popup Calendar after you have selected a date?

[S_Coupland]

Hi Good Morning (Evening).....

I have found a very useful calendar popup for excel via this forum online ....... Work a TREAT ..... one question though?

Is there any way to autohide the calendar when i have selected the date that i want?

Any Help would be greatly appreciated

Many Thanks

Simon

 

[RoryA]

What popup?

 

[S_Coupland]

What popup?

Hi Rorya,

I found a link that showed me how to create a calendar that appears on the screen when i select a cell, the calendar is created by going to Insert Active X control, More controls, and then selecting "Calendar Control 12.0" i then had to insert the following VBA code into the sheet:

Private Sub ComboBox1_Change()
End Sub
Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "dd/mm/yyyy"
ActiveCell.Select
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("A5:A10000"), Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
' select Today's date in the Calendar
Calendar1.Value = Date
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
End Sub


The result is a calendar that pops up on the page whenever i select a cell in column A, when i click the date on the calendar it adds it to the cell, the only down side is the calendar stays static on the page till i click in another cell. is there a way to automaticaly hide the pop up calendar after the date has been entered?

Many Thanks

Simon

 

[RoryA]

Change the calendar click code to

Private Sub Calendar1_Click()
    ActiveCell.Value = CDbl(Calendar1.Value)
    ActiveCell.NumberFormat = "dd/mm/yyyy"
    ActiveCell.Select
Calendar1.Visible = False
End Sub

 

[S_Coupland]

Rory,

You are a genius......

Thank You

How do i close this thread?

 

[RoryA]

You're welcome.

You don't need to do anything - we don't do that in this forum.

 

[saurav sarda]

hi,

i am new to VBA and macros...while searching for a way to have a pop-up calendar in excel for a range of cells, i came across this thread, and i tried copying the VB code in the VB editor in excel 2013, but it gives me an error saying object" does not exist"

how do i resolve this error??

thanks

 

[saurav sarda]

Hi S_Coupland,

could you help me out with the link which helped you create the pop-up in the first place...i was looking for something similar...

thanks