I've built a custom Excel add-in and I'm currently trying to figure out a way to prompt users via VBA when new versions of the add-in are available.
I tried just using the workbook_open event to check for the latest version and then prompt the user with a userform, but I discovered that when Excel loads an add-in that trigger a userform, Excel stops loading the workbook the user actually tried to open and reloads the add-in. So while the userform works like I wanted, the user gets a blank (read no sheets) Excel shell with a loaded add-in.
So I considered using Application.OnTime
to postpone the VBA until after the add-in and target file were both open. I got the impression both here and here that this is possible, but I am only able to make it work in an .xlsm
file and not a .xlam
file.
Here's the code I'm testing with:
Sub Workbook_Open()
Application.OnTime Now() + TimeValue("00:00:05"), "Test_Addin.xlam!Versioning.Notify_User"
End Sub
And in a regular code module:
Sub Notify_User()
MsgBox "Hello"
End Sub
So, my question: Am I doing something wrong here?
I'm wondering if there's something about how an add-in is loaded/designed that keeps it from allowing this type of action to be performed.
Alternatively, is there a different way to do this that you can think of?
I read an interesting blog post (and comments) by Dick Kusleika on this topic, but it sounded like some people put a version check in each sub procedure... I have a lot of procedures, so this doesn't sound like a good alternative, although I may have to resort to it.
Well, time is of the essence so I resorted to the least desirable option: a check at the beginning of each procedure.
To the interested parties, here's how I did it:
Somewhere towards the beginning of each sub procedure I put this line of code:
If Version_Check Then Call Notify_User
And in my Versioning module I put this function and procedure:
Function Version_Check() As Boolean
Installed = FileDateTime(ThisWorkbook.FullName)
Available = FileDateTime("\\NetworkDrive\Test_AddIn.xlam")
If Installed < Available Then Version_Check = True
End Function
Sub Notify_User()
Update_Check.Show
End Sub
So each time a procedure is run this code checks for a version on our corporate network with a modified datetime greater than the datetime of the installed add-in.
I'm still very open to alternative ways of checking for new versions so feel free to post an answer.