Run macro by day --- but only once per day

[Steve]

The following code executes a macro based on the day of the week. It
works well. Now I need some help modifying it so that only runs ONLY
the first time it is opened each day. Many users can access this
shared workbook and the macros put in some default informaion that some
users may want to change. Right now that info is changed back to the
default info everytime the workbook is opened each day


Private Sub Workbook_Open()
If (Weekday(Now) = vbMonday) Then [Monday]
If (Weekday(Now) = vbTuesday) Then [Tuesday]
If (Weekday(Now) = vbWednesday) Then [Wednesday]
If (Weekday(Now) = vbThursday) Then [Thursday]
If (Weekday(Now) = vbFriday) Then [Friday]

End Sub


Thanks, Steve

 

[Corey]

You could try something like :

Private Sub Workbook_Open()
If (Weekday(Now) = vbMonday) And sheet1.range("A1").value <> "Mon" Then
[Monday]
sheet1.range("A1").Value = "Mon"
If (Weekday(Now) = vbTuesday) And sheet1range("A1").Value <> "Tue" Then
[Tuesday]
sheet1.range("A1").value = "Tue"
If (Weekday(Now) = vbWednesday) And sheet1.range("A1").value <> "Wed" Then
[Wednesday]
sheet1.Range("A1").value = "Wed"
If (Weekday(Now) = vbThursday) And sheet1.range("A1").value <> "Thu" Then
[Thursday]
sheet1.range("A1").value = "Wed"
If (Weekday(Now) = vbFriday) And sheet1.range("A1").value <> "Fri" Then
[Friday]
sheet1.range("A1").value = "Fri"
' Changing Sheet1 & Range("A1") to suit....
End Sub

 

[Bob Phillips]

I would save the whole date, rather than just the day, in case there is a
week's break between running

Private Sub Workbook_Open()
If Date <> Sheet1.Range("A1").Value Then
'call the macro
Sheet1.Range("A1").Value = Date
End If
End Sub

Your code could also be simplified to

Private Sub Workbook_Open()
If Format(Date, "mmm") <> Sheet1.Range("A1").Value Then
'call the macro
Sheet1.Range("A1").Value = Format(Date, "mmm")
End If
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

You could try something like :

Private Sub Workbook_Open()
If (Weekday(Now) = vbMonday) And sheet1.range("A1").value <> "Mon" Then
[Monday]
sheet1.range("A1").Value = "Mon"
If (Weekday(Now) = vbTuesday) And sheet1range("A1").Value <> "Tue" Then
[Tuesday]
sheet1.range("A1").value = "Tue"
If (Weekday(Now) = vbWednesday) And sheet1.range("A1").value <> "Wed" Then
[Wednesday]
sheet1.Range("A1").value = "Wed"
If (Weekday(Now) = vbThursday) And sheet1.range("A1").value <> "Thu" Then
[Thursday]
sheet1.range("A1").value = "Wed"
If (Weekday(Now) = vbFriday) And sheet1.range("A1").value <> "Fri" Then
[Friday]
sheet1.range("A1").value = "Fri"
' Changing Sheet1 & Range("A1") to suit....
End Sub

The following code executes a macro based on the day of the week. It
works well. Now I need some help modifying it so that only runs ONLY
the first time it is opened each day. Many users can access this
shared workbook and the macros put in some default informaion that some
users may want to change. Right now that info is changed back to the
default info everytime the workbook is opened each day


Private Sub Workbook_Open()
If (Weekday(Now) = vbMonday) Then [Monday]
If (Weekday(Now) = vbTuesday) Then [Tuesday]
If (Weekday(Now) = vbWednesday) Then [Wednesday]
If (Weekday(Now) = vbThursday) Then [Thursday]
If (Weekday(Now) = vbFriday) Then [Friday]

End Sub


Thanks, Steve

 

[Bob Phillips]

It is hard to see whether your code is running over 2 lines or that is NG
wrap?

Do you really have 5 ranges to store whether the day has been run or not?
What happens on the following Monday say, that cell will be filled, so it
will never run again.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I would appreciate a bit more help. i worked on the suggested code and it
works perfectly for mondays but i cannot get it to work for any other day of
the week (i changed the date on my PC to try other days)
The workbook has a seperate worksheet for each workday of the week. The
named ranges refer to non contiguous cells on each day's worksheet. I
"commented" the code i need to run. when "uncommented" i get a "block if
without endif error


Private Sub Workbook_Open()
If (Weekday(Now) = vbMonday) And Sheet1.Range("mon_weather").Value = "" Then
[Monday]
'If (Weekday(Now) = vbTuesday) And Sheet2.Range("tue_weather").Value = "" Then
'[Tuesday]
'If (Weekday(Now) = vbWednesday) And Sheet3.Range("wed_Weather").Value = ""
Then
'[Wednesday]
'If (Weekday(Now) = vbThursday) And Sheet4.Range("thu_weather").Value = ""
Then
'[Thursday]
'If (Weekday(Now) = vbFriday) And Sheet5.Range("fri_weather").Value = "" Then
'[Friday]

End If

End Sub

Thanks, Steve

____________________

I would save the whole date, rather than just the day, in case there is a
week's break between running

Private Sub Workbook_Open()
If Date <> Sheet1.Range("A1").Value Then
'call the macro
Sheet1.Range("A1").Value = Date
End If
End Sub

Your code could also be simplified to

Private Sub Workbook_Open()
If Format(Date, "mmm") <> Sheet1.Range("A1").Value Then
'call the macro
Sheet1.Range("A1").Value = Format(Date, "mmm")
End If
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

You could try something like :

Private Sub Workbook_Open()
If (Weekday(Now) = vbMonday) And sheet1.range("A1").value <> "Mon" Then
[Monday]
sheet1.range("A1").Value = "Mon"
If (Weekday(Now) = vbTuesday) And sheet1range("A1").Value <> "Tue" Then
[Tuesday]
sheet1.range("A1").value = "Tue"
If (Weekday(Now) = vbWednesday) And sheet1.range("A1").value <> "Wed" Then
[Wednesday]
sheet1.Range("A1").value = "Wed"
If (Weekday(Now) = vbThursday) And sheet1.range("A1").value <> "Thu" Then
[Thursday]
sheet1.range("A1").value = "Wed"
If (Weekday(Now) = vbFriday) And sheet1.range("A1").value <> "Fri" Then
[Friday]
sheet1.range("A1").value = "Fri"
' Changing Sheet1 & Range("A1") to suit....
End Sub


The following code executes a macro based on the day of the week. It
works well. Now I need some help modifying it so that only runs ONLY
the first time it is opened each day. Many users can access this
shared workbook and the macros put in some default informaion that some
users may want to change. Right now that info is changed back to the
default info everytime the workbook is opened each day


Private Sub Workbook_Open()
If (Weekday(Now) = vbMonday) Then [Monday]
If (Weekday(Now) = vbTuesday) Then [Tuesday]
If (Weekday(Now) = vbWednesday) Then [Wednesday]
If (Weekday(Now) = vbThursday) Then [Thursday]
If (Weekday(Now) = vbFriday) Then [Friday]

End Sub


Thanks, Steve

 

[Bob Phillips]

Then you don't need the End If at all as you have no IF blocks, just single
liners.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob, it wraps because of NG formatting. all the data is cleared every
Monday AM so all cells are blank

It is hard to see whether your code is running over 2 lines or that is NG
wrap?

Do you really have 5 ranges to store whether the day has been run or not?
What happens on the following Monday say, that cell will be filled, so it
will never run again.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I would appreciate a bit more help. i worked on the suggested code and it
works perfectly for mondays but i cannot get it to work for any other

day
of

the week (i changed the date on my PC to try other days)
The workbook has a seperate worksheet for each workday of the week. The
named ranges refer to non contiguous cells on each day's worksheet. I
"commented" the code i need to run. when "uncommented" i get a

"block
if

without endif error


Private Sub Workbook_Open()
If (Weekday(Now) = vbMonday) And Sheet1.Range("mon_weather").Value =

""
Then

[Monday]
'If (Weekday(Now) = vbTuesday) And Sheet2.Range("tue_weather").Value =

""
Then

'[Tuesday]
'If (Weekday(Now) = vbWednesday) And Sheet3.Range("wed_Weather").Value

=
""

Then
'[Wednesday]
'If (Weekday(Now) = vbThursday) And Sheet4.Range("thu_weather").Value = ""
Then
'[Thursday]
'If (Weekday(Now) = vbFriday) And Sheet5.Range("fri_weather").Value =

""
Then

'[Friday]

End If

End Sub

Thanks, Steve

____________________


:

I would save the whole date, rather than just the day, in case there

is
a

week's break between running

Private Sub Workbook_Open()
If Date <> Sheet1.Range("A1").Value Then
'call the macro
Sheet1.Range("A1").Value = Date
End If
End Sub

Your code could also be simplified to

Private Sub Workbook_Open()
If Format(Date, "mmm") <> Sheet1.Range("A1").Value Then
'call the macro
Sheet1.Range("A1").Value = Format(Date, "mmm")
End If
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

You could try something like :

Private Sub Workbook_Open()
If (Weekday(Now) = vbMonday) And sheet1.range("A1").value <>

"Mon"
Then

[Monday]
sheet1.range("A1").Value = "Mon"
If (Weekday(Now) = vbTuesday) And sheet1range("A1").Value <>

"Tue"
Then

[Tuesday]
sheet1.range("A1").value = "Tue"
If (Weekday(Now) = vbWednesday) And sheet1.range("A1").value <> "Wed"
Then
[Wednesday]
sheet1.Range("A1").value = "Wed"
If (Weekday(Now) = vbThursday) And sheet1.range("A1").value <>

"Thu"
Then

[Thursday]
sheet1.range("A1").value = "Wed"
If (Weekday(Now) = vbFriday) And sheet1.range("A1").value <>

"Fri"
Then

[Friday]
sheet1.range("A1").value = "Fri"
' Changing Sheet1 & Range("A1") to suit....
End Sub


The following code executes a macro based on the day of the week. It
works well. Now I need some help modifying it so that only runs ONLY
the first time it is opened each day. Many users can access this
shared workbook and the macros put in some default informaion

that
some

users may want to change. Right now that info is changed back

to
the

default info everytime the workbook is opened each day


Private Sub Workbook_Open()
If (Weekday(Now) = vbMonday) Then [Monday]
If (Weekday(Now) = vbTuesday) Then [Tuesday]
If (Weekday(Now) = vbWednesday) Then [Wednesday]
If (Weekday(Now) = vbThursday) Then [Thursday]
If (Weekday(Now) = vbFriday) Then [Friday]

End Sub


Thanks, Steve