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

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

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

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


"Steve" wrote in message
oups.com...
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

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

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)

"Corey" wrote in message
...
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


"Steve" wrote in message
oups.com...
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

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

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)

"srolls" wrote in message
...
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

____________________


"Bob Phillips" wrote:

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)

"Corey" wrote in message
...
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


"Steve" wrote in message
oups.com...
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

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

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)

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

"Bob Phillips" wrote:

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)

"srolls" wrote in message
...
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

____________________


"Bob Phillips" wrote:

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)

"Corey" wrote in message
...
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


"Steve" wrote in message
oups.com...
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