General discussion

Locked

Calendar control in Excel

By Arockiyadoss Santhappan ·
I would like to add a calendar control to one of the cells where user needs to enter a date.

Can any one suggest a simplest possible way for the same?

Thanks.

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

data validation

by sawidji.kurniawan In reply to Calendar control in Excel

Here the steps:
1. Click on the cell where need to validate
2. Choose Data menu (alt+d)
3. Choose Validation sub-menu
4. Under Setting on Data Validation sub-menu set
validation criteria with 'date' (you could
modify any validation that you want)
5. You also could add a message or alert on that
cell

Collapse -

Try this -

by JimHM In reply to Calendar control in Excel

Here's the site page - http://www.windowsdevcenter.com/pub/a/windows/2004/04/27/excelhacks.html

It's pretty good ...

Open the workbook that you want to place the calendar in. It is a good idea to use your Personal.xls for this, in which case you should first go to Window > Unhide- PERSONAL.XLS. If this is grayed out it means you do not, as yet, have a Personal.xls. You can easily create one by recording a dummy macro. Go to Tools > Macro > Record new macro and choose Personal Macro Workbook from the Store macro in box. Then click OK, select any cell and stop recording. Excel will now have automatically created your Personal.xls. So now go to Window > Unhide- PERSONAL.XLS, and it should be there.
Now go to Tools > Macro > Visual Basic Editor (Alt+F11). Then go to Insert > UserForm from within the VBE. This should automatically display the Control Toolbox; if it does not go to View > Toolbox. Now, right click on the Toolbox and select Additional Controls. Scroll through the list until you see: Calendar Control 10.0 (the number will differ depending on the version of Excel you are using), check the checkbox and click OK. Now click the Calendar that is now part of the Toolbox and then click on the UserForm we inserted.

Using the size handles on both the UserForm and the Calendar Control, make them both a reasonable size. Now make sure the UserForm is selected, and then go to View > Properties Window (F4). Select Caption from the Properties Window and replace: UserForm1 with the word Calendar. Now go to View > Code (F7) and in the white Private Module in front of you, add the following code:
Private Sub Calendar1_Click()
ActiveCell = Calendar1.Value
End Sub

Private Sub UserForm_Activate()
Me.Calendar1.Value = Date
End Sub
Now go to Insert > Module and in this Public Module place this code:
Sub ShowIt()
UserForm1.Show
End Sub
Next, click the top right X (or push Alt+F11) to return back to Excel.
Finally, go to Tools > Macro > Macros (Alt+F and then select ShowIt, click Options, assign a shortcut key, and you're done.
All you need to do now is push your shortcut key and the Calendar will show with today's date as the default. Click any date and it will be inserted into the active cell.

Try it - I think you'll like it .. they have some great stuff ..

Collapse -

And you wonder why people don't help

by JimHM In reply to Try this -

Gee - Would be nice to know if any of the suggestions help this person - and a nice thanks to the group for assistance ...

But - Now others know why help is hard to come by because people don't - say whether it was useful or not - and if it was - Thanks ... I guess they weren't raised right... with the magic words..

Back to Community Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums