Unprotect a workbook with VBA (BUT) re-protect it when saved ???

[BaLLZaCH]

I have stumped myself again and thought maybe someone would have an "Ohhh yeah, duhh!!!" idea to help me out ...



Basically, I want the excel spreadsheet to "BE Protected" in standard form, and only be "Unprotected" when macros are enabled -- BUT DONT JUMP TO CONCLUSIONS, hear me out, it gets deeper than that ....


If the user does not enable macros, I want the book to be protected. If they do enable macros, it becomes unprotected.... (On load is good enough, no need to be real-time)..... But, they can save the book, so it would no longer be protected , which is where my question comes in ...



I could use the -unprotect "password"- on load when macros are enabled but this will only work if the spreadsheet was originally protected and is never saved again. This is a problem because this workbook will be given to others and will be saved time and time again.


So, I had a vision :-) .... function 'before save' , protect the sheet using -protect "password"- , allow the sheet to save, and then unprotect back using 'unprotect "password"' so you can continue using - BUT, the problem with this is now I have created an endless loop in theory, because after it unprotects it would want to re-save again because it changed ....




So, is there anything else I could do to accomplish this ?
(Protect workbook in excel only, unprotect workbook when macros are enable, and allow changes to be saved..)


Thanks !

[jtp]

Hi,

Try using the BeforeClose Event instead of BeforeSave

Please Login or Register  to view this content.

Hope this helps

[davesexcel]

Here is a thread discussing that topic
http://www.excelforum.com/showthread.php?t=389574

[BaLLZaCH]

Hey JTP, davesexcel, thanks for the replies...


Dave, I read the forum link, but thats not "All that" I am trying to do... - JTP, originally played with on close but still same problem, would create and endless save loop.... Let me reprhase in a shorter way:


Is there a way to "Save-As, (Protected)" on an un-protected workbook ?
(I will then be unprotecting on-load but this is not my question, my question is how to protect the 'saved' copy only & not the one actively being used...)






The perfect golden made-up example would be something like this:

'sub workbook_before save (PROTECTED as boolean)
'alerts = off
'workbook.save, as PROTECTED
'alerts = on
'end sub

That is made up foolishness but something like that would be perfect...







Thanks !

[jtp]

Well I guess you could setup some kind of button in the workbook to save the file to another location and add the protection. Have the commandbutton call this...

Please Login or Register  to view this content.

I still don't feel confident this is answering your question but its a shot.

[mikerickson]

If a workbook has been changed since it was opened, and then the workbook is closed, a prompt asking the user if they want to save will appear.

UNLESS the line

Please Login or Register  to view this content.

has been exicuted.

[BaLLZaCH]

Hey Mike, that is good line of code to know for the future... But not sure how I could use it now...

JTP, I had thought about a button to save like you suggested, but: #1 Nobody will use it, they would just go to file: save ,,,, #2 I need to save to save the "same" file, not a copy. (You know how goofy people can be , so its gotta be as easy as possible...)



So if we protect the sheet, save it, and then unprotect it, regaurdless if the thisworkbook.saved is used, or if anything was changed or not, it will still want to save again because the workbook changed (became unprotected) so there would be an endless loop... Right ?? Unless, unless it was set to auto-save on close, but that would suck because you could accidentally save a screwed up spreadsheet just by closing the application .... RRRRRr



I have a strange way of thinking sometimes but it seems to "me" like this should be an obvious option MS should have included in the SAVE command .... ?!!? Just a though ... You know, "Save-As, 'PROTECTED'"



Thanks for any help !
Later

[mikerickson]

In your situation, I would use the Before_Save event to make sure that the worksheet was protected when it is saved.

Please Login or Register  to view this content.

[BaLLZaCH]

Hey Mike, dont excite me like that haha,,,

I assume that would work great except the user has to be able to keep using the sheets after saving ... (Workbook cant be protected to keep using it.... )

So this is why I said it would create an endless loop (Unless I am missing something) because if I re-unprotect the sheet, it "changed" and will have to "save again" which would loop....

Unless like I said, I am completely missing something and overlooking something obvious ....


(See, I dont want anyone to be able to use the sheet without VB macros enabled, and if it is saved unprotected, you can load up excel without macros and use it but the totals wont work right, so I require that macros are enabled, but the only way (That I have though of) to do this is "un-protect" at startup with VB which means you obviously enabled macros - but when its saved it has to be re-protected or it was a lost cause ....)
Thanks man ,,,

[mikerickson]

That routine unprotects the sheet before ending. After Saving, Saving As or having the user cancel out of a SaveAs.
It hijacks Excel's save routine, substituitng yours (which saves only protected sheets, but leaves them unprotected after being saved.)

[jtp]

I must be the confused one here. You do have the option to set the workbook as protected when you Save-As.

When your selecting your folder/name of the file, select Tools-->General Options and you can protect the workbook there. However, I was under the impression you wanted the workbook to do all this automatically and not have the user manually set it?

Your last statement made it seem like you want to do it manually?

[jtp]

Sorry i missed those last 3 posts. Ignore me!

[BaLLZaCH]

Hrmmm, OK Mike I see what you are saying (I haven't tried your example yet) but I am confused because it looks to me like, either after save or cancel the workbook unprotects itself ... And if it unprotects AFTER a save, then that means it "changed" which would have to be "saved again" on close, creating an endless loop of saving the changed protect/unprotected workbook ...

Unless I just dont understand what you are trying to tell me ...


I really do thank you for your continued help ,,





And JTP, you are being ignored... No, Im just kidding :-) hahah, .... Thanks for helping... It's OK that you are on dial-up and your computer is slow ... haha..