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

S_Coupland

Board Regular
Joined
May 14, 2012
Messages
64
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What popup?
 

S_Coupland

Board Regular
Joined
May 14, 2012
Messages
64
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Change the calendar click code to
Code:
Private Sub Calendar1_Click()
    ActiveCell.Value = CDbl(Calendar1.Value)
    ActiveCell.NumberFormat = "dd/mm/yyyy"
    ActiveCell.Select
Calendar1.Visible = False
End Sub
 

S_Coupland

Board Regular
Joined
May 14, 2012
Messages
64

ADVERTISEMENT

Rory,

You are a genius......

Thank You

How do i close this thread?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You're welcome.

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

saurav sarda

New Member
Joined
Jul 8, 2013
Messages
2
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

New Member
Joined
Jul 8, 2013
Messages
2
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,174
Members
417,130
Latest member
Darion2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top