Userinterfaceonly

[TSW632]

Hello Excel Wizards,

In Excel 2003, I have been using the code below (in the "This
Workbook" module of the file) in a shared file so users could group
and ungroup with protection on.

Now I need to change the protection password, and I can't figure out
how to make it work with a different password. I replace the password
in the code, and then I assign the new password for worksheet
protection, save and close the file, re-boot my PC, and still get a
"Run-time error '1004': The password you supplied is not correct..."
msg when I re-open the file.

Any ideas would be greatly appreciated.

Troy

'To allow group/ungroup on protected sheet


Sub workbook_open()


Dim SH As Worksheet


For Each SH In ThisWorkbook.Worksheets


With SH
..EnableOutlining = True
..Protect Password:="ooohahhh", _
Contents:=True, _
userinterfaceonly:=True
End With

 

[Gord Dibben]

How did you "assign the new password for sheet protection"?

I would go through the workbook and unprotect each sheet, either manually or by
code using old password.

Do not protect sheets or assign any password at this time.

Save the workbook with your revised password workbook_open code.

Close and re-open. No re-boot should be necessary.

AND before doing all the above, check spelling and case.


Gord Dibben MS Excel MVP

 

[TSW632]

How did you "assign the new password for sheetprotection"?

I would go through the workbook and unprotect each sheet, either manuallyor by
code using old password.

Do not protect sheets or assign any password at this time.

Save the workbook with your revised password workbook_open code.

Close and re-open.  No re-boot should be necessary.

AND before doing all the above, check spelling and case.

Gord Dibben     MSExcelMVP












- Show quoted text -

Gord,

Thank you - that worked, and maybe my problem had something to do with
not changing the password on hidden sheets as well, on my previous
attempts. But the reason I apply protection through the tools menu on
each sheet, in addition to the macro, is because the users upon
opening the file are prompted with a security warning to enable or
disable macros. If they choose disable, and I haven't applied
protection through the tools menu as well, then protection is gone.
Is there some vba code I could use to take that option away from the
users and enable macros for the file automatically each time they open
the file?

 

[ManicMiner]

But the reason I apply protection through the tools menu on
each sheet, in addition to the macro, is because the users upon opening
the file are prompted with a security warning to enable or disable
macros. If they choose disable, and I haven't applied protection through
the tools menu as well, then protection is gone. Is there some vba code
I could use to take that option away from the users and enable macros
for the file automatically each time they open the file?

Hi TSW632,

If you think about it, if the workbook has been saved with the sheets
already password protected, they will still be protected when it is
reopened, even if macros are disabled.

You will still need to use the Workbook_Open() event to re-apply the
userinterface only parameter which is not persistent.

You should also password protect the project to make accessing the
password more difficult.

I haven't seen a native way to ensure macros are enabled, after all thats
what a virus writer would love!

Try Chip Pearson's website for a discussion on dealing with situations in
which macros are disabled.
http://www.cpearson.com/Excel/EnableMacros.aspx


HTH

 

[Gord Dibben]

There is no way to force users to enable macros other than to render the
workbook unusable should they disable macros.

The general method is when closing the workbook, to hide all sheets but one
which has a message imprinted.

"By disabling macros you have made this workbook useless. Close and re-open
with macros enabled."

With macros enabled, workbook_open code reveals the sheets and hides the message
sheet.

In other reply, ManicMiner has posted Chip's URL for the code needed to do this.


Gord

 

[TSW632]

There is no way to force users to enable macros other than to render the
workbook unusable should they disable macros.

The general method is when closing the workbook, to hide all sheets but one
which has a message imprinted.

"By disabling macros you have made this workbook useless.  Close and re-open
with macros enabled."

With macros enabled, workbook_open code reveals the sheets and hides the message
sheet.

In other reply, ManicMiner has posted Chip's URL for the code needed to do this.

Gord





- Show quoted text -

Thanks Gord, and Thanks ManicMiner!