Macro security is killing me

[roscoe]

I have an expense report template I built in Office 2003 with macros that I share with coworkers. When I shared it, some folks had macro security issues. Some work in an environment where they can't change the security settings (government offices for example). Since I did also I was able to use my govt PKI signature to sign it. Befoer that though I created my own signature. I was told (but could not verify) that folks could add that to their trusted list and not be asked anymore.

Just upgraded to 2007 and suddenly things are wonky. I tried to add my signature and Excel won't let me. No reason, just says it couldn't. So I used the help file to figure out how to create a new one, and it refers to a dialog box I can't get to (apparently pops up when you don't have a signature, but I have two...one is the one that doesn't work and I'd like to delete if I could figure out how, and the other is expiring in a few days so I'd rather not use it).

Could really use some guidance here...

Next question...I was using macros on another computer and when I opened the file I was given the coice of turning on the macros or not. My current computer doesn't ahve that option. It either turns them off (no option to overide) or turns all macros on (not safe and not what I want). How/where do I find the option to temporarily disable until I click enable? Seen it, know it exists, can't find it now.

Thanks!

 

[roscoe]

Now I'm really dorked. I found an older signature and it seemed to work. Reopened the file and Excel said the signature was invalid. SO I went back to the orignal and tried to remove the siganture, but it wouldn't let me ("Read-only workbook had been changed"...everytime I open the file and then close it it says it has been changed, but I didn't do anyything. This behavior is now blocking me from doing anything).

Told me to "enable editing" and save the changes, but I can't find how to do that (is it just me or is the help file system worthless?).

 

[roscoe]

I am locked out of this file until someone can tell me how to "enable editing" so I can resave my "changes" (there aren't any but excel thinks there are) and remove the offending signature.

(Why oh why would excel let me add it if upon reopening it it tells me it's invalid and won't let me remove it?)

 

[Greg Truby]

Sounds like you knew how to digitally sign your projects under Excel 2003, which implies you were able to create a valid digisig & you new how to have other users install your digisig so that it would allow them to run your macros. Let me know if this assumption is unfounded.

OK - first off - I would recommend trying to port the code to an add-in when you get the chance. I know, I know, Excel allows you to put code in workbooks. But long-term that is a weak design and distribution game plan. But, the reality is that if it's not already in an add-in, and it's complex, you need something to get you there...

First question: are you saving the files as XLSM?

Secondly: set your macro security to MEDIUM. That should allow you to conditionally enable unsigned workbooks.


Thirdly: If you cannot get the digisig to stick, you might try creating a TRUSTED LOCATION for your code on the users' machines (this assumes they are using Excel 2007). The path is:

• Office Button
• Excel Options
• Trust Center
• Trust Center Settings...
• Add new location...

and follow your nose from there.

Any file put into a trusted location is supposed to run, no questions asked. So be sure you never put a file with a dodgy pedigree in there.

 

[ilovedurango]

Try here?

http://www.lytebyte.com/2008/03/20/...tures-and-settings-in-excel-from-office-2007/

 

[Greg Truby]

I am locked out of this file until someone can tell me how to "enable editing" so I can resave my "changes" (there aren't any but excel thinks there are) and remove the offending signature.

Try dropping macro security all the way down (back to the trust center, but instead of Trusted Locations goto Macro Settings.)

...is it just me or is the help file system worthless?).

It ain't just you. My experience is that sometimes it seems to be designed to be unhelpful. The other day I was working offline and was trying to figure out how to paste a list of all of the names in a workbook and Excel's help was my only resource. Completely and utterly useless. (You have go to the FORMULA tab and pick the use in formula dropdown in the defined names group and then paste names..., in case you ever need to know. )

 

[roscoe]

First, Excel 2007 no longer has "medium". It has "disable with no notification", "disable with notification", "disable except digitally signed", and "enable all".

I used to have it set that it asked me if I was sure (in 2003) or would disable them until I clicked abutton (2007, different machine) but now that option doesn't exist.

I selected enable all (I hate that) and still get the same problem...it flags the file as having an invalid signature (so why did it let me sign with it?). The file opens as read only (compatibility mode) and many of the editing buttons are grayed out.

As for trusted locations, the location for this file is already in the list.

 

[Greg Truby]

First, Excel 2007 no longer has "medium". It has "disable with no notification", "disable with notification", "disable except digitally signed", and "enable all".
I used to have it set that it asked me if I was sure (in 2003) or would disable them until I clicked abutton (2007, different machine) but now that option doesn't exist.

Correct. The functional equivilant of 2003's "medium" is "disable with notification". You should get a security warning bar where you can click the "options..." button and enable the macros.

I selected enable all (I hate that) and still get the same problem...it flags the file as having an invalid signature (so why did it let me sign with it?). The file opens as read only (compatibility mode) and many of the editing buttons are grayed out.

It won't let you edit the project? Or it won't let you edit worksheets?

 

[roscoe]

It won't let you edit the project? Or it won't let you edit worksheets?

Nothing. Decided to copy and paste into another workbook and not working. Totally locked out of the file until I (apparently) find the magic command to "enable editing"

 

[Greg Truby]

In the lower left of the status bar where it says "ready". Do you see an icon that is supposed to be a worksheet being stamped with a red stamp? And when you hover it says "marked as final"?