deploying Custom.xls

[Guest]

Before going on vacation, I moved an Excel workbook and Custom.xls (where the
macros it needs) to our group's server; I wanted to keep the data and the
macro source code in one place, for ease of maintenance. I found that by
placing a shortcut to the server-based Custom.xls in the local machines
XLSTART folder, the macros would be available to the local machine even
though they resided in a central location. After much testing (including from
a reboot), I was entirely satisfied that everything worked.

Today, opening the workbook generated a message that Custom.xls was
read-only or encrypted, and the macros would not run. How could this work
perfectly in testing and not at all a week later?

I guess a more useful question is: what is the proper way to centralise VBA
code? (I would like to be able to protect it from tampering, as well, if
possible.)

Thanks!

 

[Tom Ogilvy]

It can be marked as readonly by a process outside of Excel. Maybe it is a
maintenance action performed on the server files.

 

[gimme_this_gimme_that]

The proper way to centralize VBA code is to store the VBA code in an
AddIn.
You can resolve the tampering issues by creating a Template Worksheet
and an AddIn and keeping sensitive data in the AddIn while keeping the
formatting and the "end result" Workbook in the Template.

Today, opening the workbook generated a message that Custom.xls was
read-only or encrypted, and the macros would not run. How could this work
perfectly in testing and not at all a week later?

Perhaps there was something slightly different about how it was opened
a week later.

 

[Guest]

Thanks Tom,

I thought there might be something like that.