Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home

Macro to unlock VBA Project?

Posted by Sue on January 19, 2002 1:06 AM
Can a macro contain code that will unprotect and re-protect the VBA project?
Regards,
Sue.

Check out our Excel VBA Resources

Re: Macro to unlock VBA Project?

Posted by Bariloche on January 19, 2002 7:28 AM
Sue,

I did a search of the Excel Programming newsgroup on DejaNews (Google Groups) and the short answer is "No." The only method that is generally suggested is the use of "SendKey". However, most of the time that this method is mentioned it is also indicated that "SendKeys" is unreliable. The closest example of the "SendKeys" code I was able to find is here. Unfortunately it looks like it is a work-in-progress. You might want to monitor that newsgroup and see what else comes up. There's another thread from 16Jan02 that is concerned with the same topic. Do a search within the NG and see what you come up with.


good luck


This is one way, there are a bunch!

Posted by Joe Was on January 19, 2002 10:53 AM
Private Sub Worksheet_Change(ByVal Target As Range)
'
' Macro by Joseph S. Was
'
Application.EnableEvents = False

If Target = [C33] And [C33] = "Done" Then ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="admin"
Application.EnableEvents = True
End Sub

Place the above code in the Sheet module. Then if the word "Done" is placed in Cell "C33" [in this case] the active sheet will be protected.

For the above code, I put a dropdown list in C33 with two items " Done and Edit" when done is selected the sheet is protected with the password "admin" To unprotect the sheet Tools-Protection-Password=admin. Or you can code unprotect just like the protect above, only with "UnProtect" in place of protect and the trigger being "Edit"

If you need something else than the above let me know and give a good example of what you what to happen exactly, as there are so many ways to do this. JSW


Lock and UnLock code

Posted by Joseph S. Was on January 19, 2002 11:05 AM
Sub myUnLock()
'This code will unprotect the sheet.
Application.EnableEvents = False
Application.DisplayAlerts = False
ActiveSheet.Unprotect ("admin")
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub

Place this code in a module.

Private Sub Worksheet_Change(ByVal Target As Range)
'
' Macro by Joseph S. Was
'
Application.EnableEvents = False

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="admin"
Application.EnableEvents = True
End Sub


Then this code go's in the Sheet module to re-protect after the module UnProtect code has finished. Put your code in the unprotect module. JSW

Macro by Joseph S. Was


Re: Macro to unlock VBA Project?

Posted by Ivan F Moala on January 19, 2002 12:09 PM
Sue Bariloche is correct,

however I have had success .....posted a while
ago to this board......do a seach via
http://www.alltheweb.com/index.php?cat=web&lang=english&query=

Result = 1100.html

I have altered this since.


Ivan


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.