Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unprotecting Saved Workbook Copies
I need to allow users to manipulate saved copies of a workbook. One
suggestion was to use the before save event to determine if the user is trying to "save" or "save as". If the user is trying to "save as" the macro would remove the protection on the workbook. The help menu says you must first create a new class module and declare an object of type Application With events. I don't understand the example, how do I do that? Then, the help menu states that I have to connect the declared object in the class module with the Application object. How do I do that? I don't follow the given examples. Finally, I need to write the code that determines if the user has selected "save as" and turn the workbook protection off. Any Help would be appreciated. Thanks, -Mike -- Michael J. Nowak Energy Systems/Dow Chemical Texas City, TX |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unprotecting Saved Workbook Copies
Mike,
No need for all that class approach. Open the ThisWorkbook module and paste this: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim WS As Worksheet If SaveAsUI = True Then For Each WS In ThisWorkbook.Worksheets WS.Unprotect "Password" Next End If End Sub NickHK "Mike" .(donotspam) ... I need to allow users to manipulate saved copies of a workbook. One suggestion was to use the before save event to determine if the user is trying to "save" or "save as". If the user is trying to "save as" the macro would remove the protection on the workbook. The help menu says you must first create a new class module and declare an object of type Application With events. I don't understand the example, how do I do that? Then, the help menu states that I have to connect the declared object in the class module with the Application object. How do I do that? I don't follow the given examples. Finally, I need to write the code that determines if the user has selected "save as" and turn the workbook protection off. Any Help would be appreciated. Thanks, -Mike -- Michael J. Nowak Energy Systems/Dow Chemical Texas City, TX |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unprotecting Saved Workbook Copies
Each workbook has its own beforesave event found in the ThisWorkbook module.
You can get to this through the project explorer in the VBE. when you get to that module, at the top, in the left dropdown, select Workbook in the right dropdown select BeforeSave. It will put in a declaration for the BeforeSave event. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) End Sub You can check the SaveAsUI variable to see whether it is a save or saveas Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) if SaveAsUi then ' it is a save as else ' it is a save End if End Sub -- Regards, Tom Ogilvy "Mike" wrote: I need to allow users to manipulate saved copies of a workbook. One suggestion was to use the before save event to determine if the user is trying to "save" or "save as". If the user is trying to "save as" the macro would remove the protection on the workbook. The help menu says you must first create a new class module and declare an object of type Application With events. I don't understand the example, how do I do that? Then, the help menu states that I have to connect the declared object in the class module with the Application object. How do I do that? I don't follow the given examples. Finally, I need to write the code that determines if the user has selected "save as" and turn the workbook protection off. Any Help would be appreciated. Thanks, -Mike -- Michael J. Nowak Energy Systems/Dow Chemical Texas City, TX |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unprotecting Saved Workbook Copies
Thanks for the information, Tom. Unfortunately, it is still not working. I
tried using some information boxes to see if it was attempting to activate the macro but nothing came up. Here is the code I have: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI Then ActiveWorkbook.Unprotect ActiveSheet.Unprotect End If End Sub If you can see the issue or have other comments I would really apreciate any help you can offer. Thanks, Michael Nowak -- Michael J. Nowak Energy Systems/Dow Chemical Texas City, TX "Tom Ogilvy" wrote: Each workbook has its own beforesave event found in the ThisWorkbook module. You can get to this through the project explorer in the VBE. when you get to that module, at the top, in the left dropdown, select Workbook in the right dropdown select BeforeSave. It will put in a declaration for the BeforeSave event. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) End Sub You can check the SaveAsUI variable to see whether it is a save or saveas Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) if SaveAsUi then ' it is a save as else ' it is a save End if End Sub -- Regards, Tom Ogilvy "Mike" wrote: I need to allow users to manipulate saved copies of a workbook. One suggestion was to use the before save event to determine if the user is trying to "save" or "save as". If the user is trying to "save as" the macro would remove the protection on the workbook. The help menu says you must first create a new class module and declare an object of type Application With events. I don't understand the example, how do I do that? Then, the help menu states that I have to connect the declared object in the class module with the Application object. How do I do that? I don't follow the given examples. Finally, I need to write the code that determines if the user has selected "save as" and turn the workbook protection off. Any Help would be appreciated. Thanks, -Mike -- Michael J. Nowak Energy Systems/Dow Chemical Texas City, TX |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unprotecting Saved Workbook Copies
Looks like I got it today so you can disregard my previous message. Thank
you very much! -Mike -- Michael J. Nowak Energy Systems/Dow Chemical Texas City, TX "Tom Ogilvy" wrote: Each workbook has its own beforesave event found in the ThisWorkbook module. You can get to this through the project explorer in the VBE. when you get to that module, at the top, in the left dropdown, select Workbook in the right dropdown select BeforeSave. It will put in a declaration for the BeforeSave event. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) End Sub You can check the SaveAsUI variable to see whether it is a save or saveas Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) if SaveAsUi then ' it is a save as else ' it is a save End if End Sub -- Regards, Tom Ogilvy "Mike" wrote: I need to allow users to manipulate saved copies of a workbook. One suggestion was to use the before save event to determine if the user is trying to "save" or "save as". If the user is trying to "save as" the macro would remove the protection on the workbook. The help menu says you must first create a new class module and declare an object of type Application With events. I don't understand the example, how do I do that? Then, the help menu states that I have to connect the declared object in the class module with the Application object. How do I do that? I don't follow the given examples. Finally, I need to write the code that determines if the user has selected "save as" and turn the workbook protection off. Any Help would be appreciated. Thanks, -Mike -- Michael J. Nowak Energy Systems/Dow Chemical Texas City, TX |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unprotecting Workbook | Excel Discussion (Misc queries) | |||
why do I get a warning when unprotecting a workbook | Excel Worksheet Functions | |||
how to change # or copies to print in a saved sheet? | Excel Discussion (Misc queries) | |||
Protecting/Unprotecting Workbook | Excel Programming | |||
unprotecting workbook | Excel Programming |