Excel Macro that Runs Once and Never Again and is Distributed via XLAM

I am trying to basically create an XLAM which I can distribute each time I make a revision which will open a MsgBox upon first run but then never again. I don't want end users to have to save or anything as my end users tend to open Excel purely to use simple functions like creating sequence runs to copy to clipboard then close workbook without saving.

I found this --> http://www.vbaexpress.com/kb/getarticle.php?kb_id=511

But it requires the workbook to be saved which is a problem because my users often don't save workbooks. I tried adding ActiveWorkbook.Save to the code which didn't work. I also tried putting it into ThisWorkBook and it didn't work, but I suspect the code is looking under Modules not ThisWorkBook.

Does anybody have any suggestions? I know how to write the code for the MsgBox to auto-run at start, but I have zero idea where to start to have it only run once.

I'm looking for a code only option, none of the options listed in the above URL appeal to me such as editing the workbook, creating a regedit or creating a local file.

Update:

I had an idea, could I have the Macro delete the file.xlam itself even tho it was loaded in Excel? Some sort of "Force Delete"

Solution 1:

Update:

I just got an upvote on this today and actually approached this a different way with some recent knowledge I've attained.

VBA actually has a built in save/read/update Windows Reg function, in a confined folder. I've gotten it to read/write/update elsewhere via calling shell, but I prefer the built in method when possible.

I used this https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/savesetting-statement to save a reg key stating "reset" = TRUE and the first thing my macro did was check for this and if it didn't exist, create it and do XYZ. Next run it would find the key and move along. Just another approach..

I'm sure there are many different ways to accomplish this goal and each will have merit depending on use case scenario. In my case what I ended up doing was having an XLAM File which wrote the following details (Username/Date/Revision#) to a central "update_log.txt" file on a server and then each launch checked the current revision number and the update_log.txt to confirm the user had received the MsgBox. If not, display it.

When I need to send a new message, I update the "Revision.XLAM" with a new MsgBox describing update and increment the update revision #.

The code looks like this:

Main Sub:

'''''PUBLICVARS'''''
Public blnFound As Boolean
Public UpdateRev As Variant
'''''ENDPUBVARS'''''
Public Sub UpdateMsg()

Call UpdateLogCheck ' Check and/or create Update_Log.txt
blnFound = False ' Always start false
UpdateRev = "Updated:1" & (Environ$("Username")) 'Increment with each Update
Call SearchTextFile(UpdateRev)

If Not blnFound = "True" Then
    Call Update_Msg
    Call IncrementUpdateLog(UpdateRev)
End If
End Sub

CreateUpdateLog Module:

Public Sub UpdateLogCheck()
Dim UpdateFile As Variant
UpdateFile = "\\server\update_log.txt"
Set fs = CreateObject("Scripting.FileSystemObject")
If Dir(UpdateFile, vbDirectory) = vbNullString Then
    Set a = fs.CreateTextFile(UpdateFile, False)
End If
End Sub

Search Update Log Module:

Public Sub SearchTextFile(strSearch)
     strFileName = "\\server\update_log.txt"
     ' Const strSearch = "Updated:1"
     Dim strLine As String
     Dim f As Integer
     Dim lngLine As Long
     f = FreeFile
     Open strFileName For Input As #f
     Do While Not EOF(f)
         lngLine = lngLine + 1
         Line Input #f, strLine
         If InStr(1, strLine, strSearch, vbBinaryCompare) > 0 Then
             blnFound = True
             Exit Do
         End If
     Loop
     Close #f
End Sub

The UpdateMsg Module:

Public Sub Update_Msg()

Dim line1 As Variant
Dim line2 As Variant
Dim line3 As Variant
Dim line4 As Variant
Dim line5 As Variant
Dim line6 As Variant
Dim line7 As Variant
Dim line8 As Variant
Dim line9 As Variant

line1 = "This is the 'New Revision' Message, it should only appear once when there is a new revision"
line2 = "This Revision focused on this new delivery system for Revision Updates"


    MsgBox line1 & vbCrLf & vbCrLf & _
           line2 & vbCrLf & vbCrLf & _
           line3 & vbCrLf & vbCrLf & _
           line4 & vbCrLf & vbCrLf & _
           line5 & vbCrLf & vbCrLf & _
           line6 & vbCrLf & vbCrLf & _
           line7 & vbCrLf & vbCrLf & _
           line8 & vbCrLf & vbCrLf & _
           line9 & vbCrLf & vbCrLf

End Sub

Increment/Update Log File:

Public Sub IncrementUpdateLog(UpdateRev)

Dim UpdateFile As Variant
UpdateFile = "\\server\update_log.txt"
Open UpdateFile For Append As #1
Write #1, UpdateRev
Close #1

End Sub