Announcement

Collapse
No announcement yet.

Get Current Date In VBA

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Get Current Date In VBA



    I have a calendar in my spreadsheet that returns a date selected to a cell that is active.
    When the user clicks cell D40 in worksheet Surcharges, I use a Worksheet_SelectionChange event to show the calendar. When the user selects a date it returns the date in that cell.
    Problem is I want to limit the user to select a date no more than 90 days from today's date.
    I was having a problem using today() in the vba code.
    When i enter todays date in cell E39 (-today()) and use the code below it works:
    Code:
    Private Sub Calendar1_Click()
    
        ActiveCell.Value = Calendar1.Value
      Unload Me
    
    
        If Calendar1.Value - Range("e39").Value > 90 Then
        MsgBox "over 90!"
        Range("d40").Value = ""
        Exit Sub
        End If
        
    End Sub
    But when I try to use the today() function below I get errors:
    Code:
    Private Sub Calendar1_Click()
    
    Dim x As Date
    
        ActiveCell.Value = Calendar1.Value
      Unload Me
    
    x = date(today())
    
        If Calendar1.Value - x > 90 Then
        MsgBox "over 90!"
        Range("d40").Value = ""
        Exit Sub
        End If
    
    End Sub
    What am I doing wrong? I would prefer to avoid putting =today() in a cell and refer to it. Is that even possible?
    Last edited by Dave Hawley; August 31st, 2007, 12:17. Reason: Auto Merged Doublepost

  • #2
    Re: Today() In Vba Code

    The VBA Date function does what the worksheet TODAY() function does.
    Entia non sunt multiplicanda sine necessitate.

    Comment


    • #3
      Re: Today() In Vba Code

      In VBA use "Date" to get current date, not "Today".

      Code:
      'x = date(today())
      'replace the code above with the following:
      x=Date

      Comment


      • #4
        Re: Today() In Vba Code

        OK It actually doesnt work. Datemin is empty. Function doesn't get called.
        Apologies, looks like I still need help!

        Comment


        • #5
          Re: Today() In Vba Code

          Did you read the replies from me and shg?

          Comment


          • #6


            Re: Today() In Vba Code

            Thanks!
            Below is the code that REALLY works! I even have the calendar coming up if the date is out of range to re-select
            Code:
            Private Sub Calendar1_Click()
            
            Dim datemin As Date
            
              ActiveCell.Value = Calendar1.Value
              Unload Me
            
            datemin = Date
            
                If Calendar1.Value - datemin > 90 Then
                    MsgBox "You must select a start date within 3 months of today." & Chr(10) & _
                    "Please try again", , "Invalid Selection"
                    Range("D40").Value = ""
                    Range("D40").Select
                    frmCalendar.Show
                
                End If
                
                  
            End Sub
            Originally posted by turtle44
            In VBA use "Date" to get current date, not "Today".

            Code:
            'x = date(today())
            'replace the code above with the following:
            x=Date

            Comment

            Working...
            X