Consulting

Results 1 to 4 of 4

Thread: Reset Cell Value to Zero every day (after midnight)

  1. #1
    VBAX Contributor
    Joined
    Jul 2011
    Location
    Manchester
    Posts
    142
    Location

    Reset Cell Value to Zero every day (after midnight)

    I have a workbook that has a cell that’s counts the number of times it is opened each day but need to reset the value to zero the first time it is opened after midnight.

    In short the count is used to trigger an automatic email reminder but I only want it to send once a day so when the count is 1 it will send the email but when it increases to above 1 no more emails will be sent until the following day.

    The count works fine but I have no idea how to the value in C5 back to zero each day.

    Can anybody help please? I have uploaded an example workbook.

    Thank you

    Mykal
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Instead of counting, store the date (and time if you want) of the last time an email was sent. Use the email sending routine to do this (Sheets("Admin").range("C3")=date). Thereafter, every time the file is opened, compare the (integer portion of the) stored date with Date (vba for system date) and if the difference is greater than 0, send an email (and update the stored date)(If Date-Sheets("Admin").range("C3").value>0 then…)).
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Something like this in the Workbook module could probably work


    Option Explicit
    Private Sub Wworkbook_Open()
        
        Dim LastDateSent As Date
            
        On Error GoTo FirstTIme
        LastDateSent = ThisWorkbook.CustomDocumentProperties("EmailSentDate")
        On Error GoTo 0
        
        If LastDateSent < Int(Now) Then
            MsgBox "Send email" '   logic
            ThisWorkbook.CustomDocumentProperties("EmailSentDate") = Int(Now)
        End If
        
        Exit Sub
        
    FirstTIme:
        Call ThisWorkbook.CustomDocumentProperties.Add("EmailSentDate", False, msoPropertyTypeDate, Int(Now) - 1)    '   yesterday
        Resume
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    VBAX Contributor
    Joined
    Jul 2011
    Location
    Manchester
    Posts
    142
    Location
    Hi both
    Sorry, I have been off work ill so only just come back intowork today and not had chance to try either solution as yet but wanted to saythank you to you both for your help.
    Mykal

Posting Permissions

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