Macros disabled, not by security settings?

[DS]

Good morning all,

Using Excel 2K7.

I have a workbook with a bunch of forms and code in it. When opening this
workbook, the macros are disabled by default (no warning). The Macro Security
Settings are currently set to "Enable All" and other workbooks allow macros
to run.

When selecting the macros dialogue box (with the Run/Edit/Step Into etc
options) the macros in the workbook show their "full" name (e.g. including
workbook name, module & macro name). Attempting to run results in the error
message "Because of your security settings, macros have been disabled.." and
advises me to change the security level. Everything I see against this
workbook is what I would normally see if I had the security settings set to
Disable All Without Warning.
I have tried changing the security settings to Disable All and then back to
Enable All, and it doesn't resolve the matter.

Just to make things even more interesting, if I open a different workbook
with code in it, even in the same instance of the Excel Application that has
the problem wb open in it, macros are enabled in the second workbook, but
remain disabled in the problem wb!

Any suggestions would be gratefully received...

 

[DS]

SOLVED:

Using a Windows 7 / Office 2K7 combination, if the workbook is
password-protected, the file cannot (apparently) be scanned to confirm that
the code is "safe". Therefore it's prevented from running. Solution?
Unprotect the wb, code runs fine, exactly as it should. Also works fine if
protected write access only, in both read and write modes.

Significantly less than ideal, to say the least. If anyone has any other
suggestions on this, I would be profoundly grateful to hear them. It would be
nice to use both the password feature and code...!

Cheers,
DS

 

[joel]

Two things to try

1) See if there are any Class modules.
2) Search for the error message in VBA and check the option to look i
entire Project (default is current module)

 

[Martin Brown]

SOLVED:

Using a Windows 7 / Office 2K7 combination, if the workbook is
password-protected, the file cannot (apparently) be scanned to confirm that
the code is "safe". Therefore it's prevented from running. Solution?
Unprotect the wb, code runs fine, exactly as it should. Also works fine if
protected write access only, in both read and write modes.

It can't be that. I have a number of generic spreadsheets that will run
on any version of Excel and all are password protected. You may have to
put them in a trusted location and adjust trust centre settings
accordingly, but XL2007 will permit password protected macros to run.

Regards,
Martin Brown

 

[DS]

Hi Joel,

1. No Class modules
2. Not sure what you mean by searching for the error message throughout the
project. The error message presented is an Excel error message indicating
that the macros are disabled, rather than a VBA error type.

Had a quick look at the links provided. On the first, Martin Brown states
quite confidently that "it can't be that". As I'm not registered on CodeCage,
I can't reply directly, but I can assure that it "is that". Adding a full
password stops all code in the workbook, taking the password off again allows
them to function correctly. According to a post on RDB's site, it's not a
consistent problem, but does occur - his suggested solution involves editing
the registry to bypass the issue.
http://www.rondebruin.nl/password2007.htm
The 2nd link provided returns a 404.

Cheers,
DS

 

[MikeC]

It can't be that. I have a number of generic spreadsheets that wil
run
on any version of Excel and all are password protected. You may hav
to
put them in a trusted location and adjust trust centre settings
accordingly, but XL2007 will permit password protected macros to run.

Hi Martin,

thanks for your response. It does seem that it *is* that, though i
seems as though it's not a consistent issue. I've never come across i
before in any flavour of OS / Office combination. The Trust Centre wa
the first thing I checked.

I tried the following with the wb in question, making no othe
changes:
Password present - no code will run.
Remove Password - code will run fine.
Re-add password - no code will run
Remove Password - code will run fine
Add R/W Password - code will run fine in both Read Only and full acces
modes.
Add full password - no code will run
Remove all passwords - code runs fine


I've found an article by Ron deBruin about the matter. His method o
dealing with it involves registry editing to allow the password / cod
run to remain together, and notes that it isn't consistent.
'Macros are disabled when you open password protected workbooks
(http://www.rondebruin.nl/password2007.htm)

Cheers,
Mike

 

[Martin Brown]

Hi Martin,

thanks for your response. It does seem that it *is* that, though it
seems as though it's not a consistent issue. I've never come across it
before in any flavour of OS / Office combination. The Trust Centre was
the first thing I checked.

I tried the following with the wb in question, making no other
changes:
Password present - no code will run.
Remove Password - code will run fine.
Re-add password - no code will run
Remove Password - code will run fine
Add R/W Password - code will run fine in both Read Only and full access
modes.
Add full password - no code will run
Remove all passwords - code runs fine


I've found an article by Ron deBruin about the matter. His method of
dealing with it involves registry editing to allow the password / code
run to remain together, and notes that it isn't consistent.
'Macros are disabled when you open password protected workbooks'
(http://www.rondebruin.nl/password2007.htm)

Now you have me worried.

It may be only a matter of time before my protected macros worksheets
will run into copies of XL2007 that fail to run password protected code.

I checked in REGEDIT I do not have any patches in place to override.

About identifies my current patch level of XL2007 SP2 as
Excel 12.0.6514.5000 SP2 MSO (12.0.6525.1000)
(shame you cannot cut and paste that string)

Was your iffy workbook created in XL2007 from scratch or in a previous
version and ported to XL2007. Mine are all ported from XL2002 or before.

Clutching at straws here, but it would be useful to understand what the
root cause is I don't like hacking the registry with a flint axe. Or
worse still having to instruct end users how to do it over the phone.

Can you create a minimal failing case that gives away no trade secrets?

Thinking about it I have seen something like this happen when I double
click on a version that has been autosaved after a fatal crash in Excel
(which sadly is all too common in XL2007).

Regards,
Martin Brown

 

[MikeC]

Now you have me worried.

It may be only a matter of time before my protected macros worksheets
will run into copies of XL2007 that fail to run password protecte
code.

I checked in REGEDIT I do not have any patches in place to override.

About identifies my current patch level of XL2007 SP2 as
Excel 12.0.6514.5000 SP2 MSO (12.0.6525.1000)
(shame you cannot cut and paste that string)

Was your iffy workbook created in XL2007 from scratch or in a previous
version and ported to XL2007. Mine are all ported from XL2002 o
before.

Clutching at straws here, but it would be useful to understand wha
the
root cause is I don't like hacking the registry with a flint axe. Or
worse still having to instruct end users how to do it over the phone.

Can you create a minimal failing case that gives away no trad
secrets?

Thinking about it I have seen something like this happen when I double
click on a version that has been autosaved after a fatal crash i
Excel
(which sadly is all too common in XL2007).

Regards,
Martin Brown

Hi Martin,

I created this wb in 2k7. I've never come across this issue before, an
the wb in question was working just fine in a Vista / Office 2K
combination - the problem occurred when using a Windows 7 / Office 2k
combo - after weeks of complaining to replace Vista with W7, the firs
time I try and do anything it won't work!

I'm currently running 2k7 12.0.4518.1014, MSO (12.0.6036.5000), so i
seems like I'm quite a way behind you at the moment - you never know, i
might have been addressed in some of the intervening updates.

The wb has minimal non-VBA content, just a couple of reference sheet
with no more than 50 populated cells (no formulae) on each. The cod
being used throughout isn't anything "special". There's nothing mor
advanced than some basic validation of content and some low-leve
mathematical calculations (averages of multiple entries etc). Certainl
nothing that I would expect to cause "suspicion" in a security progra
or similar (ie there's nothing creating, editing or deleting files i
sensitive locations, launching separate applications etc).

The particularly strange thing is that I can open this document (whe
password-protected) and no macros will be enabled. I can then open
separate wb through the same instance of Excel which is not passwor
protected, and macros will be enabled in that wb within the sam
instance... I've never come across that before!

Like you, I'm not keen on the idea of doing anything with the registr
in order to bypass this "feature", which is why I'm going to use a comb
where if VBA is enabled it will kick out anyone but me, and if it isn't
it will go only to the placeholder which has no functionality (ie need
to have VBA available in order to open properly).


Having done some further testing, I find that creating a new wb with
1-module content of a MessageBox.Show, adding a password will cause th
same effect. I have attached the file I've just tried (password i
"PASSWORD"). It would be interesting to see if you can open it wit
macros working - I can't.


You mention that you're using ported wbs from 2k2 - I wonder if thi
might have some relevance. The ones I'm encountering difficulty wit
have been created in 2k7 as .xlsm wbs.

Cheers,
Mik

+-------------------------------------------------------------------
|Filename: Book1.xlsm
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=413
+-------------------------------------------------------------------

 

[joel]

You can copy regedit entries by going to the menu Edit - Copy Key Name.
The actual value can be copied if you select the key name and then go t
menu Edit - Modify and copy from the edit box.

Here is one that I copied

C:\Program Files\Altiris\ALTIRI
AGENT\Agents\PatchMgmtAgent\Languages\0410\PatchMgmtAgentsUI.dl

 

[Martin Brown]

Martin Brown;613382 Wrote:

Hi Martin,

I created this wb in 2k7. I've never come across this issue before, and
the wb in question was working just fine in a Vista / Office 2K7
combination - the problem occurred when using a Windows 7 / Office 2k7
combo - after weeks of complaining to replace Vista with W7, the first
time I try and do anything it won't work!

Wind7 could be the important factor. I haven't run into a copy yet.

I'm currently running 2k7 12.0.4518.1014, MSO (12.0.6036.5000), so it
seems like I'm quite a way behind you at the moment - you never know, it
might have been addressed in some of the intervening updates.

I doubt it. I have been working on XL2007 (and muttering about its
glacial slowness at charts) since it was introduced and through all the
intervening patch levels (and some prerelease ones too).

Having done some further testing, I find that creating a new wb with a
1-module content of a MessageBox.Show, adding a password will cause the
same effect. I have attached the file I've just tried (password is
"PASSWORD"). It would be interesting to see if you can open it with
macros working - I can't.


You mention that you're using ported wbs from 2k2 - I wonder if this
might have some relevance. The ones I'm encountering difficulty with
have been created in 2k7 as .xlsm wbs.

Cheers,
Mike


+-------------------------------------------------------------------+
|Filename: Book1.xlsm |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=413|
+-------------------------------------------------------------------+

Unfortunately, the webpage won't let me in without registering and I am
not inclined to do that. My strange looking reply-to address is valid if
left unmodified. Just email me a copy of the offending spreadsheet and I
will happily take a look at it to see if it fails here.

Regards,
Martin Brown