Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Calendar

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is it possible to format a cell so that when it is clicked on, a drop down calendar displays and you can choose the date from the calendar to enter into the cell? If so, could someone describe the procedure. Thank You.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try:

    Choose Data > Validation form the menu. Choose List from the Allow box. In the source box highlight a range on the spreadsheet which contains the data you would like to appear in your list.
    It's never too late to learn something new.

    Ricky

  3. #3

    Join Date
    May 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Alternatively, you could use Excel's calendar control.

    Go to the VBE (Alt+F11), click on Tools>Additional Controls and look for Calendar Control 9.0.
    Put this control on a UserForm and also put two spinner buttons and a command button.

    Put the following in the UserForm code module :-

    Private Sub CommandButton1_Click()
    ActiveSheet.Range("A1") = Calendar1.Value
    Me.Hide
    End Sub

    Private Sub SpinButton1_Change()
    If Calendar1.Month = 12 And SpinButton1.Value > 0 Then
    Calendar1.Year = Calendar1.Year + 1
    Calendar1.Month = 1
    ElseIf Calendar1.Month = 1 And SpinButton1.Value < 0 Then
    Calendar1.Year = Calendar1.Year - 1
    Calendar1.Month = 12
    Else
    Calendar1.Month = Calendar1.Month + SpinButton1.Value
    End If
    SpinButton1.Value = 0
    End Sub

    Private Sub SpinButton2_Change()
    Calendar1.Year = Calendar1.Year + SpinButton2.Value
    SpinButton2.Value = 0
    End Sub


    Put the following in the Worksheet's code module :-

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$A$1" Then UserForm1.Show
    End Sub

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    UserForm1.Show
    Target.Offset(1, 0).Select
    End Sub


    Now whenever cell A1 is selected or double-clicked, the calendar will appear so that a date can be selected and then entered in A1 by clicking the command button.











  4. #4
    New Member
    Join Date
    May 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the post dwhj, I am trying to do it as well.

    Could you make it a bit more dynamic - I need the calendar to come up and enter the date when any cell of a specific column is double clicked.

    Thanks for the help.

  5. #5

    Join Date
    May 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-06 17:18, Fonged wrote:
    Thanks for the post dwhj, I am trying to do it as well.

    Could you make it a bit more dynamic - I need the calendar to come up and enter the date when any cell of a specific column is double clicked.

    Thanks for the help.

    Actually there was an error in the original code.
    The BeforeDoubleClick procedure should have been :-

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Address = "$A$1" Then
    UserForm1.Show
    Application.EnableEvents = False
    Target.Offset(1, 0).Select
    Application.EnableEvents = True
    End If
    End Sub


    If you want the calendar to appear, for example, when any cell in Column 2 (i.e. column B) is double-clicked :-

    Private Sub CommandButton1_Click()
    ActiveCell = Calendar1.Value
    Me.Hide
    End Sub

    Private Sub SpinButton1_Change()
    'MsgBox SpinButton1.Value
    If Calendar1.Month = 12 And SpinButton1.Value > 0 Then
    Calendar1.Year = Calendar1.Year + 1
    Calendar1.Month = 1
    ElseIf Calendar1.Month = 1 And SpinButton1.Value < 0 Then
    Calendar1.Year = Calendar1.Year - 1
    Calendar1.Month = 12
    Else
    Calendar1.Month = Calendar1.Month + SpinButton1.Value
    End If
    SpinButton1.Value = 0
    End Sub

    Private Sub SpinButton2_Change()
    Calendar1.Year = Calendar1.Year + SpinButton2.Value
    SpinButton2.Value = 0
    End Sub

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = 2 Then
    UserForm1.Show
    Target.Offset(1, 0).Select
    End If
    End Sub


    In the BeforeDoubleClick procedure, just change the column number to fit your needs.



    [ This Message was edited by: dwhj on 2002-05-06 18:36 ]

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    OKC
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    dwhj: This is cool, except when you click the spinner button to go in reverse, nothing happens, the forward button to increase the dates works fine.


  7. #7

    Join Date
    May 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-06 19:12, Eddie G. wrote:
    dwhj: This is cool, except when you click the spinner button to go in reverse, nothing happens, the forward button to increase the dates works fine.

    In the VBE go to the spin button properties window, and change the "Min" property from 0 to -1

  8. #8
    New Member
    Join Date
    May 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    the Additional Controls it's unenable

    how i can let Enable
    really nice Topic

    [ This Message was edited by: smart on 2002-05-07 05:51 ]

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    North Alabama, USA
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Smart,
    Did you Insert->User Form? It doesn't appear enabled until I do.


    _________________
    Hope this helps!

    Rocky

    "Be not the first by whom the New are try'd,
    Nor yet the last to lay the Old aside."
    Alexander Pope (1688-1744).

    [ This Message was edited by: Rocky E on 2002-05-07 06:17 ]

  10. #10

    Join Date
    May 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-07 05:49, smart wrote:
    the Additional Controls it's unenable

    how i can let Enable
    really nice Topic

    [ This Message was edited by: smart on 2002-05-07 05:51 ]

    I'll assume you're using Excel 2000.

    In the VBE, go to Tools>References and look in the drop down list for Microsoft Calendar Control 9.0
    If it's there, select it.

    If it's not there, create a reference by browsing to MSCAL.OCX
    Then go to Tools>Additional Controls and activate Calendar Control 9.0


    Note : If you are using Excel 97, you need to activate Calendar Control 8.0

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
  •