Closed Thread
Results 1 to 11 of 11

How do you create a calendar drop down in excel?

  1. #1
    Calendar drop down box in excel
    Guest

    How do you create a calendar drop down in excel?

    Does anyone know how you would create a calendar drop down box in excel?

  2. #2
    ben
    Guest

    RE: How do you create a calendar drop down in excel?

    What do You mean exactly by a "Calander 'drop down box'". If you want to have
    a calander pop up when a user clicks in a certain cell, you can create a
    userform with a calander control on it. The calander control is an add-on
    control however and will only exist on systems that have it installed. If
    it's not installed, the function will error out. To have it pop up for
    clicking certain cells, add an event to the selection change in the worksheet
    module.
    --
    When you lose your mind, you free your life.


    "Calendar drop down box in excel" wrote:

    > Does anyone know how you would create a calendar drop down box in excel?


  3. #3
    MBlake
    Guest

    Re: How do you create a calendar drop down in excel?

    As Ben mentioned you have to watch this one, I spent awhile making a
    Calendar (amazingly simple) and it worked fine. However the Calednar
    control is usually installed with MS Office Pro and not the Standard version
    we use at work. You can download the necessary file but all users must have
    the Active X file installed on their machine, my place of work will not
    allow that. If you can use the Calendar then note there are different
    versions for Office 97, 2002 and 2002 ( I think)

    Useful links include -

    http://www.ozgrid.com/VBA/excel-calendar-dates.htm

    http://www.ozgrid.com/News/excel-calendar-dates.htm

    http://www.ozgrid.com/News/excel-calendar-dates.htm

    Good luck,
    Mickey



  4. #4
    quartz
    Guest

    RE: How do you create a calendar drop down in excel?

    If you are interested, I have several VBA functions that work in the
    following way:

    1. User clicks a button on a sheet or toolbar.
    2. A formless calendar appears on the sheet.
    3. The user selects a date on the calendar.
    4. The user clicks the button again (#1 above).
    5. The program captures the date selected and deletes the calendar.

    No user form is needed. It's very clean. But, as mentioned in previous
    posts, you must have the control to begin with and so must all your users.

    If you are interested please post back and I'll post the functions needed.

    "Calendar drop down box in excel" wrote:

    > Does anyone know how you would create a calendar drop down box in excel?


  5. #5
    Create a calendar drop down box
    Guest

    RE: How do you create a calendar drop down in excel?

    I would be interested in your way of creating this. What are the functions
    that you use?

    "quartz" wrote:

    > If you are interested, I have several VBA functions that work in the
    > following way:
    >
    > 1. User clicks a button on a sheet or toolbar.
    > 2. A formless calendar appears on the sheet.
    > 3. The user selects a date on the calendar.
    > 4. The user clicks the button again (#1 above).
    > 5. The program captures the date selected and deletes the calendar.
    >
    > No user form is needed. It's very clean. But, as mentioned in previous
    > posts, you must have the control to begin with and so must all your users.
    >
    > If you are interested please post back and I'll post the functions needed.
    >
    > "Calendar drop down box in excel" wrote:
    >
    > > Does anyone know how you would create a calendar drop down box in excel?


  6. #6
    Ron de Bruin
    Guest

    Re: How do you create a calendar drop down in excel?

    I have some examples on this page if you want to see it
    http://www.rondebruin.nl/calendar.htm



    --
    Regards Ron de Bruin
    http://www.rondebruin.nl



    "Create a calendar drop down box" <[email protected]> wrote in message
    news:[email protected]...
    >I would be interested in your way of creating this. What are the functions
    > that you use?
    >
    > "quartz" wrote:
    >
    >> If you are interested, I have several VBA functions that work in the
    >> following way:
    >>
    >> 1. User clicks a button on a sheet or toolbar.
    >> 2. A formless calendar appears on the sheet.
    >> 3. The user selects a date on the calendar.
    >> 4. The user clicks the button again (#1 above).
    >> 5. The program captures the date selected and deletes the calendar.
    >>
    >> No user form is needed. It's very clean. But, as mentioned in previous
    >> posts, you must have the control to begin with and so must all your users.
    >>
    >> If you are interested please post back and I'll post the functions needed.
    >>
    >> "Calendar drop down box in excel" wrote:
    >>
    >> > Does anyone know how you would create a calendar drop down box in excel?




  7. #7
    quartz
    Guest

    RE: How do you create a calendar drop down in excel?

    Five functions are involved. I usually put the following in a separate
    module. Assign your button to call Calendar_Main() to run it. As previously
    stated, you click one time to pop the calendar up, select a date, then click
    the same button again to capture the date and delete the calendar object.

    I think mine runs a little differently from Ron's. The way this is set now,
    it will popup a message box displaying the date selected. That is where you
    would pass the date back to your calling sub. Just place all five of the
    following into one standard code module. I hope you like it.

    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

    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

    Private Function CalendarAdd()
    'ADD A FORMLESS ACTIVEX CALENDAR CONTROL TO THE ACTIVE SHEET; EXIT
    'DESIGN MODE FOR USER INTERFACE, CENTER THE OBJECT ON SCREEN; SET
    'CALENDAR VALUE EQUAL TO TODAY'S DATE;
    Application.ScreenUpdating = False
    Dim objCalendar As OLEObject
    Dim objWorkSheet As Worksheet
    Dim varCenter() As Variant
    Set objWorkSheet = Parent.ActiveSheet
    Set objCalendar = objWorkSheet.OLEObjects.Add(ClassType:="MSCAL.Calendar",
    Link:=False, DisplayAsIcon:=False)
    varCenter = ScreenCenterCompact
    objCalendar.Top = varCenter(1) - 72 'Adjust center of screen for 1/2
    height of object to center
    objCalendar.Left = varCenter(2) - 108 'Adjust center of screen for 1/2
    width of object to center
    objCalendar.Border.Weight = 3#
    objCalendar.Border.ColorIndex = 9 '1, 9, 23, 25
    objCalendar.Object.Value = Now()
    objCalendar.Visible = True
    objCalendar.Visible = False
    Application.ScreenUpdating = True
    objCalendar.Visible = True
    Set objWorkSheet = Nothing
    Set objCalendar = Nothing
    End Function

    Private Function CalendarGetName() As String
    'RETURN THE NAME OF THE CALENDAR OBJECT ON THE ACTIVE
    'SHEET IF ONE EXISTS; OTHERWISE RETURN EMPTY STRING;
    Dim lngCount As Long
    Dim lngX As Long
    lngCount = ActiveSheet.Shapes.Count
    If Not lngCount > 0 Then CalendarGetName = "": Exit Function
    For lngX = 1 To lngCount
    If UCase(Left(ActiveSheet.Shapes(lngX).Name, 8)) = "CALENDAR" Then
    CalendarGetName = ActiveSheet.Shapes(lngX).Name: Exit Function
    Next lngX
    End Function

    Private Function ScreenCenterCompact() As Variant
    'CALCULATE CENTER OF VISIBLE SCREEN;
    Dim strVisible As String
    Dim varCoordinates(2) As Variant
    strVisible = Windows(1).VisibleRange.Address
    varCoordinates(1) = (Range(Windows(1).VisibleRange.Cells(1, 1).Address).Top
    + (Range(Range(Windows(1).VisibleRange.Cells(1,
    1).Address).Offset(Range(strVisible).Rows.Count - 1,
    Range(strVisible).Columns.Count - 1).Address).Top -
    Range(Windows(1).VisibleRange.Cells(1, 1).Address).Top) / 2)
    varCoordinates(2) = (Range(Windows(1).VisibleRange.Cells(1, 1).Address).Left
    + (Range(Range(Windows(1).VisibleRange.Cells(1,
    1).Address).Offset(Range(strVisible).Rows.Count - 1,
    Range(strVisible).Columns.Count - 1).Address).Left -
    Range(Windows(1).VisibleRange.Cells(1, 1).Address).Left) / 2)
    ScreenCenterCompact = varCoordinates
    End Function

    HTH

    "Create a calendar drop down box" wrote:

    > I would be interested in your way of creating this. What are the functions
    > that you use?
    >
    > "quartz" wrote:
    >
    > > If you are interested, I have several VBA functions that work in the
    > > following way:
    > >
    > > 1. User clicks a button on a sheet or toolbar.
    > > 2. A formless calendar appears on the sheet.
    > > 3. The user selects a date on the calendar.
    > > 4. The user clicks the button again (#1 above).
    > > 5. The program captures the date selected and deletes the calendar.
    > >
    > > No user form is needed. It's very clean. But, as mentioned in previous
    > > posts, you must have the control to begin with and so must all your users.
    > >
    > > If you are interested please post back and I'll post the functions needed.
    > >
    > > "Calendar drop down box in excel" wrote:
    > >
    > > > Does anyone know how you would create a calendar drop down box in excel?


  8. #8
    Tom Ogilvy
    Guest

    Re: How do you create a calendar drop down in excel?

    Wow, that link isn't dominated with advertisements like that Ozgrid link.

    In fact, there are no advertisements.

    --
    Regards,
    Tom Ogilvy


    "Ron de Bruin" <[email protected]> wrote in message
    news:%[email protected]...
    > I have some examples on this page if you want to see it
    > http://www.rondebruin.nl/calendar.htm
    >
    >
    >
    > --
    > Regards Ron de Bruin
    > http://www.rondebruin.nl
    >
    >
    >
    > "Create a calendar drop down box"

    <[email protected]> wrote in message
    > news:[email protected]...
    > >I would be interested in your way of creating this. What are the

    functions
    > > that you use?
    > >
    > > "quartz" wrote:
    > >
    > >> If you are interested, I have several VBA functions that work in the
    > >> following way:
    > >>
    > >> 1. User clicks a button on a sheet or toolbar.
    > >> 2. A formless calendar appears on the sheet.
    > >> 3. The user selects a date on the calendar.
    > >> 4. The user clicks the button again (#1 above).
    > >> 5. The program captures the date selected and deletes the calendar.
    > >>
    > >> No user form is needed. It's very clean. But, as mentioned in previous
    > >> posts, you must have the control to begin with and so must all your

    users.
    > >>
    > >> If you are interested please post back and I'll post the functions

    needed.
    > >>
    > >> "Calendar drop down box in excel" wrote:
    > >>
    > >> > Does anyone know how you would create a calendar drop down box in

    excel?
    >
    >




  9. #9
    Create a calendar drop down box
    Guest

    Re: How do you create a calendar drop down in excel?

    That worked perfectly!!! Thank you so much. D

    "Ron de Bruin" wrote:

    > I have some examples on this page if you want to see it
    > http://www.rondebruin.nl/calendar.htm
    >
    >
    >
    > --
    > Regards Ron de Bruin
    > http://www.rondebruin.nl
    >
    >
    >
    > "Create a calendar drop down box" <[email protected]> wrote in message
    > news:[email protected]...
    > >I would be interested in your way of creating this. What are the functions
    > > that you use?
    > >
    > > "quartz" wrote:
    > >
    > >> If you are interested, I have several VBA functions that work in the
    > >> following way:
    > >>
    > >> 1. User clicks a button on a sheet or toolbar.
    > >> 2. A formless calendar appears on the sheet.
    > >> 3. The user selects a date on the calendar.
    > >> 4. The user clicks the button again (#1 above).
    > >> 5. The program captures the date selected and deletes the calendar.
    > >>
    > >> No user form is needed. It's very clean. But, as mentioned in previous
    > >> posts, you must have the control to begin with and so must all your users.
    > >>
    > >> If you are interested please post back and I'll post the functions needed.
    > >>
    > >> "Calendar drop down box in excel" wrote:
    > >>
    > >> > Does anyone know how you would create a calendar drop down box in excel?

    >
    >
    >


  10. #10
    Registered User
    Join Date
    03-22-2010
    Location
    Columbia, Missori
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Rolling task of events driven by date?

    I have a rolling task list for days events that must be accomplished company wide. I would like a way for my excel list to drop off these items listed in a column by date. Basically I want at midnight tonight 3/22/10 to drop out of excel and be replaced by the next days task, so now 3/23/10 would be my first column.

  11. #11
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: How do you create a calendar drop down in excel?

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1