CODEWITHSUNDEEP
excelvba
TimmyDoubleHand
TimmyDoubleHand

Reputation: 13

Checking if excel was turned on for the first time today(VBA)

I have a function difference_dat (returns in days the difference of current date and given date). The result (increases by 1 every day) is in A1.

I am going to create a table and search for information in it. I came up with Number of tables = difference_dat & checking if the worksheet was run for the first time today (each subsequent run on the same day does not matter). However, I have no idea how to check this.

Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
          i = Range("A2").Value + 1
  End If
End Sub

This code would be ok, except that when you run Excel, the entire worksheet recalculates and this disqualifies the result. At the same time, I use other events at startup, so I can't turn them off. Worksheet.SelectionChange also does not pass the result (does not detect the selection when starting Excel).

Cheers

Upvotes: 1

Views: 124

Answers (4)

Siddharth Rout
Siddharth Rout

Reputation: 149277

Here is a simple way which writes to Comments in the BuiltinDocumentProperties

Option Explicit

Private Sub Workbook_Open()
    With ThisWorkbook.BuiltinDocumentProperties("Comments")
        If DateValue(.Value) = Date Then
            '~~> Was opened earlier today
        Else
            .Value = Date
            ThisWorkbook.Save
            DoEvents
        End If
    End With
End Sub

Upvotes: 1

Алексей Р
Алексей Р

Reputation: 7627

You can use registry and events:

' in the workbook module
Private Sub Workbook_Open()
    IsFirstToday
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)  'if the workbook has not been closed overnight
    If Sh.Name = "Sheet1" Then IsFirstToday
End Sub

' registry root is 'Computer\HKEY_CURRENT_USER\Software\VB and VBA Program Settings'
Private Sub IsFirstToday()
    LastDate = GetSetting(appname:="MyExcelFile", section:="Startup", _
               Key:="LastDate", Default:="01.01.1900")
    If CDate(LastDate) < Date Then
        Debug.Print "Sheet1 activated for the first time today"
        SaveSetting appname:="MyExcelFile", section:="Startup", _
            Key:="LastDate", setting:=Date
    Else
        Debug.Print "Sheet1 activated NOT for the first time today"
    End If
End Sub

The registry entry looks like this
enter image description here

Upvotes: 0

Variatus
Variatus

Reputation: 14373

You might use a Workbook_Open event procedure like this one:-

Private Sub Workbook_Open()
    ' 289
    
    Dim Ws  As Worksheet
    
    Set Ws = Worksheets("STO_68477673")         ' specify the sheet here
    With Ws.Cells(1, 1)                         ' specify the cell here
        If .Value <> Date Then
        
                
            ' do your updating here
            MsgBox "Updated"
            

            .Value = Date
            .NumberFormat = "dd mmmm, yyyy"     ' change to suit
        End If
    End With
End Sub

The update won't run more than once a day, regardless of how often the workbook is opened and closed. However, you can force an update by deleting the date in A1 and then run the procedure.

Upvotes: 1

Xyloz Quin
Xyloz Quin

Reputation: 178

  1. Store the Date "Somewhere" way out of the way maybe in a hidden sheet (Get it named ranges pun).

  2. Check it on open

    IF Range("Somewhere").Value = Date then Exit Sub

  3. Print the result somewhere out of the way what ever metrics you need from this

  4. Update the date and have it auto save immediately so no tampering

enter image description here

You see a named range doesn't need to have a get activesheet to be updated, you just call the named range, that's why we named it.

Upvotes: 1

Related Questions