How to id a new month? 
Author Message
 How to id a new month?

I want to put some code in the OnOpen procedure to say 'if it is the first
day of the month, or the firswt time the workbook has been opened after the
first day of the month, then run a certain routine'.

Anyone know how this could be achieved?

Any ideas much appreciated, thanks, Mark



Fri, 25 Nov 2005 03:08:02 GMT  
 How to id a new month?
You need a Workbook_Open event macro. I believe you also need to save the time the workbook was
last opened in a worksheet cell somewhere. Let's say that's Sheet1!K105

Sub Workbook_Open()
  Dim LastDate As Date
  Dim Today As Date

  Today = Date
  LastDate = Worksheets("Sheet1").Range("K105").Value

  If Today > LastDate And Month(Today) <> Month(LastDate) Then
    MySub
  End If
  Worksheets("Sheet1").Range("K105").Value = Today

End Sub

Quote:

>I want to put some code in the OnOpen procedure to say 'if it is the first
>day of the month, or the firswt time the workbook has been opened after the
>first day of the month, then run a certain routine'.

>Anyone know how this could be achieved?

>Any ideas much appreciated, thanks, Mark



Fri, 25 Nov 2005 04:30:44 GMT  
 How to id a new month?
That month function is nifty to kniw, thanks again Myrna, kind regards, Mark


Quote:
> You need a Workbook_Open event macro. I believe you also need to save the

time the workbook was
Quote:
> last opened in a worksheet cell somewhere. Let's say that's Sheet1!K105

> Sub Workbook_Open()
>   Dim LastDate As Date
>   Dim Today As Date

>   Today = Date
>   LastDate = Worksheets("Sheet1").Range("K105").Value

>   If Today > LastDate And Month(Today) <> Month(LastDate) Then
>     MySub
>   End If
>   Worksheets("Sheet1").Range("K105").Value = Today

> End Sub



> >I want to put some code in the OnOpen procedure to say 'if it is the
first
> >day of the month, or the firswt time the workbook has been opened after
the
> >first day of the month, then run a certain routine'.

> >Anyone know how this could be achieved?

> >Any ideas much appreciated, thanks, Mark



Fri, 25 Nov 2005 06:21:31 GMT  
 How to id a new month?
On Sun, 08 Jun 2003 22:30:44 -0500, Myrna Larson

Myrna beat me to this one. I was going to offer the same basic
suggestion, but with one modification; I'd be inclined to store the
date that the workbook was last opened in a custom document property
rather than in a cell. The reasons for doing that are:
   - It won't get in the way of your operational data that way;
   - It lowers the chance that the data will be corrupted or
accidentally deleted.

The general principle remains the same, however:

Private Sub Workbook_Open()

Dim prp_DteOpened As Object
Dim LastDate As Date

On Error Resume Next

Set prp_DteOpened = _
 ThisWorkbook.CustomDocumentProperties("OpenedDate")

'If the property doesn't exist, create it.
If prp_DteOpened Is Nothing Then

    Set prp_DteOpened = _
     ThisWorkbook.CustomDocumentProperties.Add( _
     Name:="OpenedDate", _
     LinkToContent:=False, _
     Type:=msoPropertyTypeDate, _
     Value:=Date)

End If

On Error GoTo 0

LastDate = prp_DteOpened.Value

MsgBox "Continue with Myrna's code. " _
 & "Property value is: " & LastDate

End Sub

Quote:
>You need a Workbook_Open event macro. I believe you also need to save the time the workbook was
>last opened in a worksheet cell somewhere. Let's say that's Sheet1!K105

>Sub Workbook_Open()
>  Dim LastDate As Date
>  Dim Today As Date

>  Today = Date
>  LastDate = Worksheets("Sheet1").Range("K105").Value

>  If Today > LastDate And Month(Today) <> Month(LastDate) Then
>    MySub
>  End If
>  Worksheets("Sheet1").Range("K105").Value = Today

>End Sub

>>I want to put some code in the OnOpen procedure to say 'if it is the first
>>day of the month, or the firswt time the workbook has been opened after the
>>first day of the month, then run a certain routine'.

>>Anyone know how this could be achieved?

>>Any ideas much appreciated, thanks, Mark

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *


Fri, 25 Nov 2005 06:28:09 GMT  
 How to id a new month?
Thanks very much for that hank, regards, Mark

--

______________________________

Mark Stephens

Managing Director
Expat Gold - planning made easy:)
Tel: +44 161 8345256   Fax: +44 870 1399769
Singapore handphone: +65 9677 0684


Quote:
> On Sun, 08 Jun 2003 22:30:44 -0500, Myrna Larson

> Myrna beat me to this one. I was going to offer the same basic
> suggestion, but with one modification; I'd be inclined to store the
> date that the workbook was last opened in a custom document property
> rather than in a cell. The reasons for doing that are:
>    - It won't get in the way of your operational data that way;
>    - It lowers the chance that the data will be corrupted or
> accidentally deleted.

> The general principle remains the same, however:

> Private Sub Workbook_Open()

> Dim prp_DteOpened As Object
> Dim LastDate As Date

> On Error Resume Next

> Set prp_DteOpened = _
>  ThisWorkbook.CustomDocumentProperties("OpenedDate")

> 'If the property doesn't exist, create it.
> If prp_DteOpened Is Nothing Then

>     Set prp_DteOpened = _
>      ThisWorkbook.CustomDocumentProperties.Add( _
>      Name:="OpenedDate", _
>      LinkToContent:=False, _
>      Type:=msoPropertyTypeDate, _
>      Value:=Date)

> End If

> On Error GoTo 0

> LastDate = prp_DteOpened.Value

> MsgBox "Continue with Myrna's code. " _
>  & "Property value is: " & LastDate

> End Sub

> >You need a Workbook_Open event macro. I believe you also need to save the

time the workbook was

- Show quoted text -

Quote:
> >last opened in a worksheet cell somewhere. Let's say that's Sheet1!K105

> >Sub Workbook_Open()
> >  Dim LastDate As Date
> >  Dim Today As Date

> >  Today = Date
> >  LastDate = Worksheets("Sheet1").Range("K105").Value

> >  If Today > LastDate And Month(Today) <> Month(LastDate) Then
> >    MySub
> >  End If
> >  Worksheets("Sheet1").Range("K105").Value = Today

> >End Sub



> >>I want to put some code in the OnOpen procedure to say 'if it is the
first
> >>day of the month, or the firswt time the workbook has been opened after
the
> >>first day of the month, then run a certain routine'.

> >>Anyone know how this could be achieved?

> >>Any ideas much appreciated, thanks, Mark

> ---------------------------------------------------------
> Hank Scorpio
> scorpionet who hates spam is at iprimus.com.au (You know what to do.)
> * Please keep all replies in this Newsgroup. Thanks! *



Sat, 26 Nov 2005 00:55:50 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Update old id with new id from 3rd table

2. New month without carrying forward data from previous month

3. Please help me on Invoice ID in a format of Year-Month-SequentialNumber

4. Please help me on Invoice ID in a format of Year-Month-SequentialNumber

5. Disabling Outlook 2000 Menu Items - Finding Control ID, IDs, ID's

6. 1st month, 2nd month, 3rd month...

7. Lost Contacts because new id in exchange?

8. Digital ID: Unable to Sign New Message

9. Get ID value for new record

10. Getting ID from new record


 
Powered by phpBB® Forum Software © phpBB Group