| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
| Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
| Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
| how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
| Start Macro / Stop Macro / Restart Macro | Excel Programming | |||
Linear Mode
