Hi.
First create a userform and put a control calendar upon it. There are 3
macros in the userform module :
At initialization :
Private Sub UserForm_Initialize()
If IsDate(ActiveCell.Value) Then
Calendar1.Value = ActiveCell.Value
Else
Calendar1.Value = Date
End If
End Sub
The calendar control is set to the activecell date if there is one, else it
is set to today.
Private Sub cmdClose_Click()
Unload Me
End Sub
If the closing cross is clicked, the userform disappears
Private Sub Calendar1_Click()
ActiveCell.Value = Calendar1.Value
Unload Me
End Sub
If the calendar control is clicked, the active cell is set to the
corresponding date and the userform disappears.
-----
In the ThisWorkbook module :
Private Sub Workbook_Open()
Dim NewControl As CommandBarControl
'assigns the macro opencalendar to the Ctrl+Shift+C (may be different with
standard keyboard ?)
Application.OnKey "+^{C}", "Module1.OpenCalendar"
'Define a new control into the cell menu
Set NewControl = Application.CommandBars("Cell").Controls.Add
' defines new control characteristics
With NewControl
'name
.Caption = "Date ?"
'associated macro
.OnAction = "Module1.OpenCalendar"
'begin new group (line separation)
.BeginGroup = True
End With
End Sub
When the file opens. Have a look at the detailed comments.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Cell").Controls("Date ?").Delete
End Sub
The command is removed from the cell menu.
-----
In Module1 :
Sub OpenCalendar()
frmCalendrier.Show
End Sub
Displays userform.
Feel free if you want further comments.
Best regards.
Daniel
> -----Message d'origine-----
> De : excel-macros@googlegroups.com [mailto:excel-
> mac...@googlegroups.com] De la part de CA NARENDRA ASHARA
> Envoyé : lundi 6 juillet 2009 18:30
> À : excel-macros@googlegroups.com
> Objet : [Norton AntiSpam] $$Excel-Macros$$ Daniel -- calendar pop-up
>
>
> HI Daniel,
>
> Excel file with date function attached by you is really nice. How do I
> make
> it available to every excel file i.e. I want to use it in all workbook.
>
> Kindly guide me. Thanks in advance.
>
> Thanks & Regards,
> CA Narendra Ashara | M.Com., A.C.A. |
>
>
> -----Original Message-----
> From: excel-macros@googlegroups.com [mailto:excel-
> mac...@googlegroups.com]
> On Behalf Of Daniel
> Sent: 05 July, 2009 9:35 PM
> To: excel-macros@googlegroups.com
> Subject: $$Excel-Macros$$ Re: calendar pop-up
>
> Look at the attached file.
> Daniel
>
>
> > -----Message d'origine-----
> > De : excel-macros@googlegroups.com [mailto:excel-
> > mac...@googlegroups.com] De la part de Lynn Envoyé : dimanche 5
> > juillet 2009 17:56 À : MS EXCEL AND VBA MACROS Objet : [Norton
> > AntiSpam] $$Excel-Macros$$ Re: calendar pop-up
> >
> >
> > I don't understand what you are trying to tell me. I don't the
> command
> > "Date ?" either.
> >
> > Could you further explain?
> >
> > On Jul 4, 10:21 pm, "Daniel" <dcolarde...@free.fr> wrote:
> > > Did you have a look at my workbook. The command "Date ?" is added
> to
> > the
> > > cell right click menu when the file is opened. It is removed when
> > > the
> > file
> > > closes.
> > >
> > > Daniel
> > >
> > >
> > >
> > > > -----Message d'origine-----
> > > > De : excel-macros@googlegroups.com [mailto:excel-
> > > > mac...@googlegroups.com] De la part de Lynn Envoyé : samedi 4
> > > > juillet 2009 14:25 À : MS EXCEL AND VBA MACROS Objet : [Norton
> > > > AntiSpam] $$Excel-Macros$$ Re: calendar pop-up
> > >
> > > > this is not what I wanted. I do not want the calendar to be shown
> > on
> > > > the excel sheet.
> > > > It should only pop-up when I click on the cell which I need to
> > enter a
> > > > date.
> > > > Pls review this. thanks
> > >
> > > > On Jul 4, 2:52 am, Dilip Pandey <dilipan...@gmail.com> wrote:
> > > > > Hi Lynn,
> > >
> > > > > Calender control has been explained in the attached workbook.
> > > > > Note:- Date will be inserted in the selected cell, which can be
> > > > modified.
> > > > > check up the code and you will understand the logic, It is
> quite
> > > > simple.
> > > > > Thanks,
> > > > > --
> > > > > DILIP KUMAR PANDEY
> > > > > MBA-HR,B COM(Hons.),BCA
> > > > > Mobile: +91 9810929744
> > > > > dilipan...@gmail.com
> > > > > dilipan...@yahoo.com
> > > > > New Delhi - 110062
> > >
> > > > > On Fri, Jul 3, 2009 at 9:35 PM, Lynn <moley_c...@yahoo.com.au>
> > wrote:
> > >
> > > > > > How can I have a calendar pop-up box for me to make date
> > selection
> > > > > > when I click on a cell?
> > >
> > > > > Using Calender Control in Excel.xls
> > > > > 31KViewDownload- Hide quoted text -
> > >
> > > > > - Show quoted text -- Hide quoted text -
> > >
> > > - Show quoted text -
> >
> > >
>
>
>
>
>
> >
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 5,000 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~----------~----~----~----~------~----~------~--~---
