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!

 

[mburrows]

Back again with this...

So I completely forgot the major restraint on this was that it is a shared workbook, and the whole reason for the main line:

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

was to get around any overwriting clashes. This means the workbook can't be saved, which was my workaround
Unfortunately I can't use the registry edit as it is disabled by the administrator and don't want to go down the path of having the admin enabled on everyone's pc and someone accidentally mess something up.

That said, I would like to revisit the Workbook_Open() potential solution, as I think this has legs.

Public Sub Workbook_Open()

    Select Case Application.UserName
    
        Case "List of names"
            RefreshTime = Now + TimeValue("00:00:10")
            Application.OnTime RefreshTime, "ReOpen"
                Debug.Print "Timer Set as: " & RefreshTime
        Case Else
    End Select
End Sub
' ############################################
Sub ReOpen()

If IsUserFormLoaded("AddInfoForm") Then
Else
    Application.DisplayAlerts = False
    Workbooks.Open Workbooks("ThisWorkbook").FullName
    Application.DisplayAlerts = True
End If

End Sub

This runs perfectly whent the workbook is first opened. But after running ReOpen once, it doesnt happen again, and I don't understand why not. Why isn't excel noticing that the file has been opened again and so run Workbook_Open again?

Thanks again!