Calendar Control

R

Russ At Index

Well-known Member

Joined

Sep 23, 2002

Messages

706

Office Version

1. 365
2. 2016

Platform

1. Windows
2. MacOS

• Nov 14, 2005

• #2

Hi Colmcg.

Try this link , go to the bottom of the page &
you get the option to download.

It offers a pop up calender by way of selecting cell >
right click > option insert date.

http://www.fontstuff.com/vba/vbatut07.htm

Hope this helps !!

Russ

 

Upvote 0

C

colmcg

Board Regular

Joined

Jul 2, 2004

Messages

101

• Nov 14, 2005

• #3

Russ,

Thanks for your quick response. I saw this link from another thread, however I am trying to avoid having to 'right click'. If possible I would like the calendar to show when the cell is the active cell having been selected either by a single mouse click or if the arrow keys were used.

Basically I want to force the user to enter a date in this cell but not have to rely on them right clicking or using a shortcut.

Any ideas?

 

Upvote 0

Greg Truby

MrExcel MVP

Joined

Jun 19, 2002

Messages

10,025

• Nov 14, 2005

• #4

Just drop a calendar control straight on the worksheet (use the more controls button on the controls toolbox toolbar). If you name the cell in question something clever like CalendarCell and set the calendar's LinkedCell property to be CalendarCell then you simply need a single line of code in the worksheet's code module:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    Me.Calendar1.Visible = (Target.Address = [CalendarCell].Address)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Not as fancy as the nice userform-based solution described at fontstuff.com -- but quick and easy.

HTH

 

Upvote 0

C

colmcg

Board Regular

Joined

Jul 2, 2004

Messages

101

• Nov 14, 2005

• #5

Greg,

Your right not, quite as fancy but it does the trick.

One more question if I may. How do I get the control to close automatically after selecting a date. At the moment I have to select another cell before it closes.

Thanks

Colin

 

Upvote 0

Greg Truby

MrExcel MVP

Joined

Jun 19, 2002

Messages

10,025

• Nov 14, 2005

• #6

You could use the Click event handler on the control. At least for me changing the month or year does not generate a click event. Note that this means that the user has to "come back" if he mis-clicks the date (which is probably fine - just thought I'd mention it).

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Calendar1_Click()
    Calendar1.Visible = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

Upvote 0

C

colmcg

Board Regular

Joined

Jul 2, 2004

Messages

101

• Nov 14, 2005

• #7

Greg,

That works for me too, thanks very much.

Sorry to be a pain but I have noticed that once the cell is clicked, when the calendar shows it displays the month/year last used. i.e. if the last date selected was in March 2006 then this month and year is displayed when the cell is next clicked.

Can I add a further piece of code to make it default to today's date when the control is activated?

Thanks for your assistance

Colin

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Nov 14, 2005

• #8

Colin

Try this.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Calendar1.Visible = (Target.Address = [CalendarCell].Address)
   Calendar1.Value = Date
End Sub

 

Upvote 0

C

colmcg

Board Regular

Joined

Jul 2, 2004

Messages

101

• Nov 14, 2005

• #9

Norie,

Spot on. Works a treat. Thanks very much.

I've one last question. The calendar control works great in this particular workbook as there is only one date to be input. However I was thinking of applying this control in another workbook where the dates appear in each row in column A. Again I am trying to ensure that the user only inputs dates into this column.

Is there a way to get this calendar control to show whenever a cell is selected in column A and then it would input the selected date into this active cell.

Colin

 

Upvote 0

C

colmcg

Board Regular

Joined

Jul 2, 2004

Messages

101

• Nov 14, 2005

• #10

Norrie,

Sorry the last piece of code you posted doesn't work properly. When the calendar shows, today's date is the default date and when I select my desired date it appears in A1 and the calendar control closes. However when I click into another cell the date changes in cell A1 to today's date.

Can you help please.

Colin

 

Upvote 0