Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Creating Drop down Calendar in excel

Software Development


Reply
 
Thread Tools Search this Thread
  #1  
Old 15-07-2011
Member
 
Join Date: Jun 2011
Posts: 77
Creating Drop down Calendar in excel
  

Please anyone provide necessary solution. Actually I want to make a Drop down calendar in excel for my personal needs and I donít know how to do it. I thought of trying different ways by searching over the internet but I did not instead I thought of asking you experts. How to make it? Thank you in advance.

Reply With Quote
  #2  
Old 15-07-2011
Member
 
Join Date: Apr 2009
Posts: 562
Re: Creating Drop down Calendar in excel

To create a Drop down calendar in excel, first you have to create a new user form and then in that add a calendar from tools after that add a command button and code for the command are as follows:

Code:
Private Sub cmdClose_Click()
   Unload Me
End Sub
Code:
 Private Sub Calender1_Click()
        ActiveCell.Value = Calender1.Value
        Unload Me
End Sub
Code:
 
Private Sub UserForm_Initialize()
    If IsDate (ActiveCell.Value) Then
            Calender1.Value = DateValue (ActiveCell.Value)
    Else
            Calender1.Value = Date
    End If
Code:
Sub OpenCalender()
      frmCalender.Show
End Sub
Reply With Quote
  #3  
Old 15-07-2011
Member
 
Join Date: May 2009
Posts: 517
Re: Creating Drop down Calendar in excel

After adding calendar in the user form you may need an additional option such as shortcut key for that calendar. Below is the command for the shortcut feature for your calendar:

Code:
Private Sub WorkBook_Open ()
Application.Onkey "+^{C}", "Module1.OpenCalender"
End Sub
Reply With Quote
  #4  
Old 15-07-2011
Member
 
Join Date: May 2009
Posts: 523
Re: Creating Drop down Calendar in excel

If you want to Adding the Calendar to the Shortcut Menu then you have to use vba for it. The code is as follows:

Code:
Dim NewControl as CommandBarControl
Add the next line of code below your shortcut key code. It will tell the Excel that the variable is a new item tat to be added to the right-click menu of the cell
Code:
      Set NewControl = Application.CommandBars("Cell").Controls.Add
Next line is for some code to describe the new item:
Code:
      With NewControl
            .Caption = "Insert Date"
            .OnAction = "Module1.OpenCalendar"
            .BeginGroup = True
      End With
Reply With Quote
  #5  
Old 15-07-2011
Member
 
Join Date: May 2009
Posts: 497
Re: Creating Drop down Calendar in excel

I would like to assure you that the caption in the text that appears on the menu can be anything you desire. Just keep in mind that OnAction correctly specify your macro name including location. BeingGroup option puts a separator line over the new item on the menu.

Code:
Private Sub WorkBook_Open ()
Application.Onkey "+^{C}", "ThisWorkbook.OpenCalender"
Set NewControl = Application.CommandBars ("Cell").Controls.Add
With NewControl
                .caption = "Insert Date"
                .OnAction = "Module1.OpenCalender"
                .BeginGroup = True
End With
End Sub
Reply With Quote
  #6  
Old 15-07-2011
Member
 
Join Date: Apr 2009
Posts: 477
Re: Creating Drop down Calendar in excel

If you are interested in popup calendar then copy paste the following code. You must copy the code in the Worksheet module and then Right click on the sheet tab and choose view code at last paste the below code in the sheet module and press Alt-Q to go back to Excel.

Code:
Private Sub Calendar1_Click()
    ActiveCell.Value = CDbl(Calendar1.Value)
    ActiveCell.NumberFormat = "mm/dd/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("A1:A20"), 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
Reply With Quote
  #7  
Old 16-07-2011
Member
 
Join Date: May 2009
Posts: 602
Re: Creating Drop down Calendar in excel

To make it convenient for you I have separated the module. You have to assign your button to call Calendar_Main() to run it after that click again on the same button to capture the date.


Code:
Private Sub Calendar_Main()
'MAIN CALLING PROGRAM: ADDS/RETRIEVES/DELETES CALENDAR CONTROL;
'THE FUNCTION IS RUN TWICE: ONCE TO LOAD THE CONTROL AND ONCE
'TO CAPTURE THE VALUE AND UNLOAD THE CONTROL; ON THE FIRST CALL
'NO VALUE HAS BEEN ASSIGNED YET (EXIT SUB);
Dim dteCalendarValue As Date
dteCalendarValue = CalendarPopupProgram
If UCase(dteCalendarValue) = "12:00:00 AM" Then Exit Sub
'Optionally run other procedures here - pass the date back to other routines
'instead of just displaying the date captured (as in the following line);
MsgBox Format(dteCalendarValue, "MM/DD/YYYY")
End Sub
Reply With Quote
  #8  
Old 16-07-2011
Member
 
Join Date: May 2009
Posts: 518
Re: Creating Drop down Calendar in excel

Also there is another alternative if the above function donít work for you than you can use the below :

Code:
Private Function CalendarPopupProgram() As Date
'CREATE/DELETE CALENDAR ACTIVEX CONTROL
Dim strCalendarName As String
Dim dteCalendarValue As Date
'If calendar exists: obtain the selected date and delete the calendar object
strCalendarName = CalendarGetName
If strCalendarName <> "" Then
dteCalendarValue = ActiveSheet.OLEObjects(strCalendarName).Object.Value
ActiveSheet.Shapes(strCalendarName).Delete
CalendarPopupProgram = DateSerial(Year(dteCalendarValue),
Month(dteCalendarValue), Day(dteCalendarValue))
End If
'If calendar does not exist: create it
If strCalendarName = "" Then Call CalendarAdd
End Function
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Creating Drop down Calendar in excel"
Thread Thread Starter Forum Replies Last Post
Lock drop down cell in Microsoft Excel Thedevotee MS Office Support 2 15-02-2012 04:48 PM
Excel 2010 - drop down menus help MadHatter Windows Software 1 09-02-2012 11:07 PM
How to convert date from Gregorian Calendar to Hijri Calendar in Excel AsceTic! MS Office Support 2 07-02-2012 06:50 PM
How to hide drop down arrows in Excel Rao's Windows Software 2 07-01-2012 08:26 PM


All times are GMT +5.5. The time now is 09:27 PM.