Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Calendar Date Entry

  1. #1
    Guest

    Default

    Does anyone know how I can get Excel to give me a dropdown calendar to choose from when I want to enter a date?

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Keaau, Hawaii
    Posts
    238
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Open excel
    Right click on the little picture of the excel page in the upper left corner
    At the bottom choose view code
    If the project window is not visable on the left side go to the top of the screen and anywhere in the grey right click and make sure Standard is checked
    Hover the mouse over the icons till you find the Project explorer, click this
    Right click in the Project explorer and chose insert
    then choose UserForm
    Double click on userform
    On the toolbox that just popped up right click and choose additional controls
    Go find Calendar control 8.0
    If it is not there go to the top of the screen and click on Tools
    Then References
    Then go find Microsoft excel 8.0 object library
    now go back to your userform do the right click thing again and find the control Calendar 8.0
    Drag if, drop it on your form and size it to fit, leave room near the bottom for a finished button
    place a button on your form and lable it Finished
    Right click on your form choose View code and place this code in the window that pops up

    Private Sub Calendar1_Click()
    Dim Dy, Dt
    Dy = Calendar1.Value
    Dt = Calendar1.Value
    Dy = Format(Dy, "dddd")
    Dt = Format(Dt, "mmmm-dd-yyyy")

    Range("D1") = Dy
    Range("I1") = Dt

    End Sub
    Private Sub ExitCalendar_Click()
    Unload UserForm1
    End Sub
    Private Sub UserForm_Initialize()
    Me.Calendar1.Today
    End Sub

    On the sheet your form will appear on create a button and call it Calendar

    Right click it choose view code and past this code there

    Public Sub Calendar_Click()
    Load UserForm1
    UserForm1.Show
    End Sub

    Ta Da A calendar that sets to today upon open and will place the day and date on your sheet.

  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not to take away from Kightmares good
    efforts....BUT you can also place this control on a worksheet via the control toolbox > Morecontrols Button.


    Ivan

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry for hijacking your question.


    Ivan, I have never tried that! Does work in the same way as having it on a UsereForm?



    Ok, cool loks like it does ) My next question is can I make it stay on the Control toolbar? It only seems to be available under "Additional controls" (



    _________________
    Kind Regards
    Dave Hawley
    OzGrid Business Applications
    Microsoft Excel/VBA Training


    [ This Message was edited by: Dave Hawley on 2002-03-12 19:45 ]

  5. #5
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ivan, I have never tried that! Does work in the same way as having it on a UsereForm?



    Ok, cool loks like it does ) My next question is can I make it stay on the Control toolbar? It only seems to be available under "Additional controls" (



    _________________
    Kind Regards
    Dave Hawley
    OzGrid Business Applications
    Microsoft Excel/VBA Training


    [ This Message was edited by: Dave Hawley on 2002-03-12 19:45 ]
    Thats a good Question Dave...never really thought about making it stay on a comm bar?
    But yes your right it doesn't.....should look
    @ commands behind this...

    Ivan

  6. #6
    Guest

    Default

    Thanks very much for your help!


    On 2002-03-12 19:04, KniteMare wrote:
    Open excel
    Right click on the little picture of the excel page in the upper left corner
    At the bottom choose view code
    If the project window is not visable on the left side go to the top of the screen and anywhere in the grey right click and make sure Standard is checked
    Hover the mouse over the icons till you find the Project explorer, click this
    Right click in the Project explorer and chose insert
    then choose UserForm
    Double click on userform
    On the toolbox that just popped up right click and choose additional controls
    Go find Calendar control 8.0
    If it is not there go to the top of the screen and click on Tools
    Then References
    Then go find Microsoft excel 8.0 object library
    now go back to your userform do the right click thing again and find the control Calendar 8.0
    Drag if, drop it on your form and size it to fit, leave room near the bottom for a finished button
    place a button on your form and lable it Finished
    Right click on your form choose View code and place this code in the window that pops up

    Private Sub Calendar1_Click()
    Dim Dy, Dt
    Dy = Calendar1.Value
    Dt = Calendar1.Value
    Dy = Format(Dy, "dddd")
    Dt = Format(Dt, "mmmm-dd-yyyy")

    Range("D1") = Dy
    Range("I1") = Dt

    End Sub
    Private Sub ExitCalendar_Click()
    Unload UserForm1
    End Sub
    Private Sub UserForm_Initialize()
    Me.Calendar1.Today
    End Sub

    On the sheet your form will appear on create a button and call it Calendar

    Right click it choose view code and past this code there

    Public Sub Calendar_Click()
    Load UserForm1
    UserForm1.Show
    End Sub

    Ta Da A calendar that sets to today upon open and will place the day and date on your sheet.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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