How Run a Macro First Time the workbook is opened in a date

[aedctalk]

Hi! so i want to run a macro the first time i open the workbook each date.

(not everytime i open the workbook)

I don't know how though. I"m at a completely loss. Any helpful code or thoughts would be much appreciated

Thanks! Let me know if more claifiication is needed :/

 

[littleiitin]

Create sheet named "Status" and in workbookopen write the below code

Private Sub Workbook_Open()
 Dim rngFindTodaysDate As Range
 With ThisWorkbook.Worksheets("Status")
 
    On Error GoTo X
    Set rngFindTodaysDate = .Range("A1").End(xlDown).Find(Date)
    If rngFindTodaysDate Is Nothing Then
        .Range("A" & .Range("A" & Rows.Count).End(xlUp).Row + 1) = Date
 
'''''  your Code  Here
 
    End If
 End With
X:
End Sub

 

[aedctalk]

thank you!!!! soo much!!

works amazing! appreciate your time and help :D

 

[aedctalk]

Actually one question.

Our business closes at around 2am. So sometimes people will open this program at around 1230 in the morning. I'd like to run the macro the first time the program is opened each day.. being that its 8am or later.



So if B1 =TIME(8,35,10)

how could i insert the B1 into the above VBA to get it to run appropriately?

Please? Thanks so much :/

 

[littleiitin]

Try this:

Private Sub Workbook_Open()
 Dim rngFindTodaysDate As Range
 With ThisWorkbook.Worksheets("Status")
 
    On Error GoTo X
    Set rngFindTodaysDate = .Range("A1").End(xlDown).Find(Date)
    If rngFindTodaysDate Is Nothing Then
        .Range("A" & .Range("A" & Rows.Count).End(xlUp).Row + 1) = Date
        .Range("B" & .Range("B" & Rows.Count).End(xlUp).Row + 1) = Format(Time(), "hh:mm:ss")
 
[COLOR=red]'''''  your Code  Here[/COLOR]
        MsgBox "x"
 
    ElseIf Not rngFindTodaysDate Is Nothing And Format(rngFindTodaysDate.Offset(, 1), "hh:mm:ss") < Format("8:00:00", "hh:mm:ss") Then
 
        .Range("A" & .Range("A" & Rows.Count).End(xlUp).Row + 1) = Date
        .Range("B" & .Range("B" & Rows.Count).End(xlUp).Row + 1) = Format(Time(), "hh:mm:ss")
[COLOR=red]'''''  your Code  Here[/COLOR]
        MsgBox "x"
 
    End If
 End With
X:
End Sub

 

[aedctalk]

Thank you for your continued help on this.

So when i use your first code. It works It runs every single time its a new day.

However when i try the 2nd (bottom code) that adds in the extra 8 hours... then for some reason it does not work for me at all.

It never runs the close day macro.

Is it writing values to cells? Could it be that my page is protected?

Do i need to insert the date into any cells? like a1?

Thank you so much for helping me to get this working!!!

 

[littleiitin]

If you have protected the sheet without password then use below code. If you have given some password for protection then Where Protect and unProtect is written give a spece and write your password in double quotes : .Unprotect "xyz" and .Protect "xyz"

Private Sub Workbook_Open()
 Dim rngFindTodaysDate As Range
 With ThisWorkbook.Worksheets("Status")
     .Unprotect
    On Error GoTo X
    Set rngFindTodaysDate = .Range("A1").End(xlDown).Find(Date)
    If rngFindTodaysDate Is Nothing Then
        .Range("A" & .Range("A" & Rows.Count).End(xlUp).Row + 1) = Date
        .Range("B" & .Range("B" & Rows.Count).End(xlUp).Row + 1) = Format(Time(), "hh:mm:ss")
 
'''''  your Code  Here
        MsgBox "x"
 
    ElseIf Not rngFindTodaysDate Is Nothing And Format(rngFindTodaysDate.Offset(, 1), "hh:mm:ss") < Format("8:00:00", "hh:mm:ss") Then
 
        .Range("A" & .Range("A" & Rows.Count).End(xlUp).Row + 1) = Date
        .Range("B" & .Range("B" & Rows.Count).End(xlUp).Row + 1) = Format(Time(), "hh:mm:ss")
'''''  your Code  Here
        MsgBox "x"
 
    End If
    .Protect
 End With
X:
End Sub

 

[aedctalk]

Thank you! I'm still having another issue with this though.

If i put closeday (which is a macro) at the top..... it runs close day every single time i open the program.


If i put nothing at the top and then put closeday at the bottom .. then the macro never runs.. even after multiple days.


How could i get it to run only first time the program opens each day though? given that its after 8am :/

Do i maybe need it to clear all stuff in range "a" and range "b" before it runs the below code?

What exactly does the top part look for? and what is the bottom part looking for ?

Thank you!!!!!!!!!!!!!!!!

If you have protected the sheet without password then use below code. If you have given some password for protection then Where Protect and unProtect is written give a spece and write your password in double quotes : .Unprotect "xyz" and .Protect "xyz"

Private Sub Workbook_Open()
 Dim rngFindTodaysDate As Range
 With ThisWorkbook.Worksheets("Status")
     .Unprotect
    On Error GoTo X
    Set rngFindTodaysDate = .Range("A1").End(xlDown).Find(Date)
    If rngFindTodaysDate Is Nothing Then
        .Range("A" & .Range("A" & Rows.Count).End(xlUp).Row + 1) = Date
        .Range("B" & .Range("B" & Rows.Count).End(xlUp).Row + 1) = Format(Time(), "hh:mm:ss")
 
CloseDay
 
 
    ElseIf Not rngFindTodaysDate Is Nothing And Format(rngFindTodaysDate.Offset(, 1), "hh:mm:ss") < Format("8:00:00", "hh:mm:ss") Then
 
        .Range("A" & .Range("A" & Rows.Count).End(xlUp).Row + 1) = Date
        .Range("B" & .Range("B" & Rows.Count).End(xlUp).Row + 1) = Format(Time(), "hh:mm:ss")
'''''  your Code  Here
        MsgBox "x"
 
    End If
    .Protect
 End With
X:
End Sub

 

[littleiitin]

Hi,

1: you have to put your close day macro in both places.
2: The upper part checks whether there is any entry for today. if there is no entry then it runs else if there is an entry it will go and check the second half it also looks if Time is before 8:00 am then it runs else Exit.

Hope this will help you.