Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Get Current Date In VBA

  1. #1
    Join Date
    12th April 2007
    Posts
    204

    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:
    VB:
    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:
    VB:
    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 at 12:17. Reason: Auto Merged Doublepost

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    Re: Today() In Vba Code

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

  3. #3
    Join Date
    14th July 2007
    Posts
    759

    Re: Today() In Vba Code

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

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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    12th April 2007
    Posts
    204

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    14th July 2007
    Posts
    759

    Re: Today() In Vba Code

    Did you read the replies from me and shg?

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    12th April 2007
    Posts
    204

    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
    VB:
    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 
    
    
    Quote Originally Posted by turtle44
    In VBA use "Date" to get current date, not "Today".

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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Check Cell Has Date & Insert Current Static Date
    By thunderbolt in forum EXCEL HELP
    Replies: 7
    Last Post: June 27th, 2008, 11:21
  2. Default Pivot Table Date Field To Today/Current Date
    By G8tor4life in forum EXCEL HELP
    Replies: 4
    Last Post: June 5th, 2008, 04:24
  3. Replies: 7
    Last Post: May 30th, 2008, 02:26
  4. Replies: 3
    Last Post: September 22nd, 2007, 08:53
  5. Replies: 1
    Last Post: June 21st, 2007, 04:12

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
  •  
porno