Adding a value to a cell when opening the workbook on a new day

[sofas]

Welcome. Is there a formula or code that can do this, but only when opening the file every morning, because I may open it several times a day?

Private Sub Workbook_Open()
Sheet1.[A1] = Sheet1.[A1] + 1
End Sub

 

[Micron]

Yes, but you would need a helper cell somewhere. You open the wb and code drops in the date value in the helper cell for that day. Each time you open the wb, the code checks if the date value of that cell is the same as the current date or not and acts accordingly. There may be other more complicated methods that don't involve a helper cell but why go there?

 

[Jaafar Tribak]

Building on Micron's idea, you could store the last open date in a workbook hidden Name. This is probably a cleaner method.

Use something along these lines :

Code goes in the ThisWorkbook Module:

Option Explicit

Private Sub Workbook_Open()
    Call IncrementDailyOpenCounter(UpdateCell:=Sheet1.[a1])
End Sub

Private Sub IncrementDailyOpenCounter(ByVal UpdateCell As Range)

    On Error Resume Next
    Debug.Assert [DateStamp]
   
    If Err Then
        Call Me.Names.Add("DateStamp", Date, False)
        GoTo Update
    End If
   
    If Date > [DateStamp] Then
        Me.Names("DateStamp").Value = CLng(Date)
        GoTo Update
    End If
   
    Exit Sub
Update:
    UpdateCell = UpdateCell + 1&
    Me.Save

End Sub

 

[sofas]

Building on Micron's idea, you could store the last open date in a workbook hidden Name. This is probably a cleaner method.

Use something along these lines :

Code goes in the ThisWorkbook Module:

Option Explicit

Private Sub Workbook_Open()
    Call IncrementDailyOpenCounter(UpdateCell:=Sheet1.[a1])
End Sub

Private Sub IncrementDailyOpenCounter(ByVal UpdateCell As Range)

    On Error Resume Next
    Debug.Assert [DateStamp]
  
    If Err Then
        Call Me.Names.Add("DateStamp", Date, False)
        GoTo Update
    End If
  
    If Date > [DateStamp] Then
        Me.Names("DateStamp").Value = CLng(Date)
        GoTo Update
    End If
  
    Exit Sub
Update:
    UpdateCell = UpdateCell + 1&
    Me.Save

End Sub

Thank you, great idea