VBA to run the first time each month a spreadsheet is opened.

[Godders199]

Hello, I am looking to automate some VBA to prevent the need for users to remember to run a VBA to update the data within a workbook.

I do not believe i can use task schedulers as all computers have to be turned off when not in use.

Effectively what i am looking for is code that will run the VBA the first time a workbook is opened each month, and a message to pop up to let the user know that this is happening.

Is this possible? if yes any links to examples would be appreciated.

 

[Scott T]

In order to know if the spread sheet is updated you could keep the date it was updated in a hidden sheet, I used sheet9 here change to what you want. Then you can check if the current month of is after the month of the last time the file was auto updated.


This should go into the ThisWorkbook module

Private Sub Workbook_Open()

If Month(Date) > Month(Range("A1")) Then


[COLOR=#008000]    'code to update numbers here[/COLOR]
    Sheets("sheet9").Range("A1") = Date
    MsgBox ("spreadsheet updated")
End If


End Sub

 

[steve the fish]

You could use a 'Parameters' sheet where you store the current month number. Lets say in cell A1 you have 11. Then use:

Private Sub Workbook_Open()

If Sheets("Parameters").Range("A1").Value <> Month(Now) Then
    Sheets("Parameters").Range("A1").Value = Month(Now)
    Call Macro1
End If

End Sub

This will only run if the month is not equal to the cell value and if it does run it updates the cell so it doesnt run again until next month.

 

[My Aswer Is This]

That should work. I was thinking of an answer but had not thought of this one.

You could use a 'Parameters' sheet where you store the current month number. Lets say in cell A1 you have 11. Then use:

Private Sub Workbook_Open()

If Sheets("Parameters").Range("A1").Value <> Month(Now) Then
    Sheets("Parameters").Range("A1").Value = Month(Now)
    Call Macro1
End If

End Sub

This will only run if the month is not equal to the cell value and if it does run it updates the cell so it doesnt run again until next month.

 

[Godders199]

You could use a 'Parameters' sheet where you store the current month number. Lets say in cell A1 you have 11. Then use:

Private Sub Workbook_Open()

If Sheets("Parameters").Range("A1").Value <> Month(Now) Then
    Sheets("Parameters").Range("A1").Value = Month(Now)
    Call Macro1
End If

End Sub

This will only run if the month is not equal to the cell value and if it does run it updates the cell so it doesnt run again until next month.

Thanks for your help