Macro security is killing me

R

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,062
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
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?).
 
Upvote 0
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?)
 
Upvote 0
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.

:warning: 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. :warning:
 
Upvote 0
roscoe said:
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.
Click to expand...
Try dropping macro security all the way down (back to the trust center, but instead of Trusted Locations goto Macro Settings.)

roscoe said:
...is it just me or is the help file system worthless?).
Click to expand...
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. :banghead: (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. :rolleyes:)
 
Upvote 0
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.
 
Upvote 0
roscoe said:
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.
Click to expand...
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.

roscoe said:
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.
Click to expand...
It won't let you edit the project? Or it won't let you edit worksheets?
 
Upvote 0
Greg Truby said:
It won't let you edit the project? Or it won't let you edit worksheets?
Click to expand...

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"
 
Upvote 0
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"?
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Colleagues can't use my macro enabled workbook
Replies
8
Views
530
MARK858
MARK858
SandsB
  • Question Question
Move from shared drive to Sharepoint - killed my macro
Replies
7
Views
161
Alex Blakenburg
A
S
  • Solved
How to find my VBA function or subroutine
2
Replies
13
Views
319
Fluff
Fluff
J
  • Solved
Excel Selenium VBA - Help with loop when class appears more than once
Replies
3
Views
212
Anthony47
A
I
  • Question Question
macros blocked but all security settings appear to be correct
Replies
2
Views
372
IMPOSSIBLE7925
I

Forum statistics

Threads
1,223,912
Messages
6,175,348
Members
452,638
Latest member
Oluwabukunmi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top