Automatically run macro upon opening - with a twist

[mburrows]

Hi guys,

Background: I have created an excel file which allows my collegues to input various bits of news, and wanted to make it as simple to use, with restrictions, so use a userform. I also wanted them all to be able to keep it open, so made it a shared document.

In order to get around the problem of overwriting each others work i have a simple "Refresh" macro which simply reopens the file (upon adding data with the userform it saves the file).

Sub Refresh()

If IsUserFormLoaded("AddInfoForm") Then     ' Stop refresh if userform is open
    Call ReRefresh
Else
    Application.DisplayAlerts = False
    Workbooks.Open ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
    Application.DisplayAlerts = True
End If


'function to see if userform is open
Function IsUserFormLoaded(ByVal UFName As String) As Boolean
    Dim UForm As Object
     
    IsUserFormLoaded = False
    For Each UForm In VBA.UserForms
        If UForm.Name = UFName Then
            IsUserFormLoaded = True
            Exit For
        End If
    Next
End Function

I would now like to be able to have the file "refresh" itself automatically every 30 minutes.


My attempt:

Private Sub Workbook_Open()
Call ReRefresh
End Sub


Sub ReRefresh()
Application.OnTime Now + TimeValue("00:30:00"), "Refresh"
End Sub

Everything seems to work fine, except that the worksheet refreshes once, and then doesnt seem to actually Call ReRefresh

Any help would be much appreciated!

Thanks!

 

[baitmaster]

Refresh or ReRefresh?

Also, you should create a global variable called refreshTime that holds the ontime value you want to use. If the user closes the workbook you can use this variable to cancel the refresh code, else the file could reopen itself and run again. ALWAYS do this with time-based macros, else you risk entering macro Inception...

 

[mburrows]

I would like it to call ReRefresh, as that is the delayed sub. So once refresh runs, the file reopens itself, and basically a timer is set for it to open again in 30 minutes.

I'll just put another if statement around refresh to test if the workbook is already open or not to fix the inception.

 

[baitmaster]

You can name your code either, but at the moment it looks like you're using 2 names where only 1 exists. Code can be set to run itself so just use one of them

It's not enough to test if the file is open. Your code is telling Excel to run the next piece of code at time x - if your file is then closed, Excel will reopen the file in order to run that code. Your test will then see that the file is open...

This gets really annoying if your code then triggers the code to run again, and you have to be careful when working with these features because badly-written routines can end up triggering themselves exponentially. The only way to manage this is to cancel the instruction to rerun the code, when the file is closed. To do this, you need to know the exact time the instruction is set for, so you have to track it with a global variable

There's a free download at Case studies & example files - Spreadsheet Wizard Ltd that contains explanation and code that will provide the features you want

 

[mburrows]

I understand. Thanks for pointing this out to me. I'll have another go.

Do you have any idea why what I was trying to do wasn't working though? Ie why

Private Sub Workbook_Open()
Call ReRefresh
End Sub

Wasn't working after running:

Workbooks.Open ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

But was working when opening the file for the first time.

 

[baitmaster]

The Workbook_Open event relates solely to the file containing the code. It will run once, when that file is opened. It is not related to the opening of any other file

You would need to embed a separate call after the file>open code, e.g.

Workbooks.Open ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
Call ReRefresh

The demo in that download I pointed you at should show you (via the immediate window of the VB Editor, view > immediate window) when it is being called. Treat the file>open element as a separate thing altogether

 

[mburrows]

Just got back to working on this again. I'm definitely closer: I have got it to automatically reopen itself, now I can't stop it, as you warned.

Code to refresh:

Public dtRunTme As Date
Public Const strTime As String = "00:00:10"

Public Sub ReOpen()
    
    Application.DisplayAlerts = False
    Call SetTimer
    Workbooks.Open ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
    Application.DisplayAlerts = True


End Sub

Public Sub SetTimer()

dtRunTme = Now + TimeValue(strTime)
Application.OnTime dtRunTme, "ReOpen"
Debug.Print "settimer running"

End Sub

In order to try to stop it opening upon ordinary close i tried:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call ReOpenOff

End Sub

Public Sub ReOpenOff()

On Error Resume Next ' in case not scheduled
    Application.OnTime earliesttime:=dtRunTme, procedure:="ReOpen", schedule:=False
On Error GoTo 0

End Sub

But this doesn't seem to be working. Any ideas?

Thanks

 

[baitmaster]

I can't see anything obviously wrong. You're using the variables consistently and I think the syntax is correct

You could try turning the error handling off until you know its working, one of the down sides of error handling is that it masks other problems, I use it as sparingly as possible - so in this case I've switched it off as soon as I'm finished with it

You could also use debug.print to report the variables to the immediate window, to make sure they are correct at all times. This is usually my main starting point when investigating unknown errors

Note that you are setting the variable values when running one piece of code and then using them later in another. If anything happens that results in code being terminated in the interim, you will have lost the values in those variables so your code will fail. Debug.print will show this, as will switching off the error handling

 

[mburrows]

Great advice, found the problem by using debug.print (something i never used, would always use a msgbox, with obvious pitfalls)

Basically when I try to run the ReOpenOff sub, the variable for dtRunTme is 00:00:00, as it was lost when the workbook was reopened.

Is there anyway to save variables in VBA?

A work around I've used and is working is writing the time to a cell, saving and then reopening. When the ReOpenOff sub is called it takes the time in the cell.

 

[baitmaster]

You can't save variables once you close your session, but you could write data to the Windows registry instead. Chip Pearson has an in-depth article on it that includes examples Registry Functions

Your current workaround is fairly standard for those times that data needs to be passed from session to session. You could also hold them in defined names - like named ranges but they refer to a value instead of a range