How to make Excel VBA Automation Execute for All End Users

Question

Hello:

I wrote the following code so that when an Excel spreadsheet is closed it will update its name with the current date and time:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If ThisWorkbook.Name = "Name_Last Opened-" & Format(Date, "MM-DD-YYYY") & _
      "_" & Format(Time, "HH.MM") & ".xls" Then
    Else
        ThisWorkbook.SaveAs Filename:="\\C:\... Name_Last Opened-" & _
            Format(Date, "MM-DD-YYYY") & "_" & Format(Time, "HH.MM") & ".xls"
        FName = Sheets("Name").Range("D1").Text
        Kill FName
    End If
End Sub

Private Sub Workbook_Open()
    Range("A1").Select
    ActiveCell.FormulaR1C1 = ThisWorkbook.Name
End Sub

Additionally, the code is located within VBAProject(Name of file), under MS Excel Object - ThisWorkbook.

This code works perfectly for me or the workstation that it was created on; however, it does not execute for anyone who opens it on their worstation. Would anyone know how to get the code to execute whenever the spreadsheet is opened and closed from any computer, not just mine?

Thank you,

DFM

Answer 1

It's possible that Excel's security settings aren't allowing other people's computers to run the script that could be interpreted as risky malware. Perhaps you changed your security settings so long ago that you forgot about it. See if you can modify another user's security settings to see if that will make the macro execute on the workbook close.

Answer 2

Date() function needs administrator access to run.. so if your user is a non admin, then it will fail. Instead use now(). Most of the times this is some thing which we usually forget as we(people developing the tool) have admin access over our PC's

Answer 3

"Would anyone know how to get the code to execute whenever the spreadsheet is opened and closed from any computer, not just mine?"

I don't think it can be done with 100% certainty unless you can ensure that every possible user will have macro security set such that your macro can execute.

Assuming you can get past that one, you should perhaps check that the users all have the worksheet in the same hard-coded path on C:\ that you seem to be using. What happens if they open the workbook from a different location?

Also:

FName = Sheets("Name").Range("D1").Text

is getting a value from one place and

Range("A1").Select
ActiveCell.FormulaR1C1 = ThisWorkbook.Name

is putting it in another.

I think I'd try something like the following (which assumes from your code that you actually only want to change the file name if it has not changed since the minute of the current time changed):

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim dateTime As String
Dim oldPath As String
Dim newPath As String

    dateTime = Format(Now, "MM-DD-YYYY_HH.MM") ' Format the while thing in one string - once

    With ThisWorkbook

        oldPath = .FullName ' what is it called now, and where did it come from?
        newPath = .Path & "\" & "Name_Last Opened-" & dateTime & ".xls" ' what should it be called now?

        If oldPath <> newPath Then ' only do something if not saved in last minute - is that what you really want?
            .SaveAs Filename:=newPath
            Kill oldPath
        End If

    End With

End Sub

Answer 4

Fundamentally, you cannot ensure that all users will a) have a macro security setting of low or medium, and b) if set to medium, enable them when the file is opened.

Creating your own certificate would seem like the obvious answer, but in practice I find that the resultant messages and warnings are even more confusing/frightening for some end users, leading to much the same situation as with macro security. Third-party certificates avoid this, but are $$$ and almost surely overkill for an Excel workbook in a corporate environment.

What I've done where I need users to have VBA enabled is to set all sheets to xlveryhidden on save, except a custom locked sheet that only has a note saying macros must be enabled and a brief guide on how to do this. This sheet is hidden and the others restored by the workbook's workbook_open procedure, something that of course will not fire if VBA is disabled.