Is there a way to enable/disable the VBA password and lock project for
viewing, using a macro?

Dylan

Hi Dylan,

Assuming you know the password, the following code, based on work by fellow
MVP Bill Manville, will unprotect/protect a vba project:

Sub UnprotectVBProj(ByVal Pwd As String)
Dim vbProj As Object
Set vbProj = ThisWorkbook.VBProject
If vbProj.Protection < 1 Then Exit Sub ' already unprotected
Set Application.VBE.ActiveVBProject = vbProj
SendKeys Pwd & "~~"
Application.VBE.CommandBars(1).FindControl(ID:=257 8, recursive:=True).Execute
End Sub

Sub ProtectVBProj(ByVal Pwd As String)
Dim vbProj As Object
Set vbProj = ThisWorkbook.VBProject
If vbProj.Protection = 1 Then Exit Sub ' already protected
Set Application.VBE.ActiveVBProject = vbProj
SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "{TAB}" & Pwd & "~"
Application.VBE.CommandBars(1).FindControl(ID:=257 8, recursive:=True).Execute
End Sub

Cheers

--
macropod
[MVP - Microsoft Word]


"dd" <dd.dd wrote in message ...
| Is there a way to enable/disable the VBA password and lock project for
| viewing, using a macro?
|
| Dylan
|
|

Hi macropod

macropod, your original version probably works fine, but due to my lack of
knowledge, I don't know how to run it !!

I want to assign it to a custom menu button, but can't see the macro in the
list of available macros.

If I remove the (ByVal Pwd As String), add Dim Pwd As String, and change
Sendkeys to include the confirm password. When I now run it, the Dialog box
remains on the screen.

How do I use the original code?
How do I okay the dialog?

Sub ProtectVBProj() ' to allow it to appear as a macro
'Sub protectVBProj(ByVal Pwd As String) 'not sure how to call this as a
macro?
Dim Pwd As String
Dim vbProj As Object
Set vbProj = ThisWorkbook.VBProject
If vbProj.Protection = 1 Then Exit Sub ' already protected
Set Application.VBE.ActiveVBProject = vbProj
SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "12345" & "{TAB}" & Pwd & "12345"
'However, the dialog remains on the screen - how do I update it.
Application.VBE.CommandBars(1).FindControl(ID:=257 8,
recursive:=True).Execute
End Sub


"macropod" wrote in message
...
Hi Dylan,

Assuming you know the password, the following code, based on work by fellow
MVP Bill Manville, will unprotect/protect a vba project:

Sub UnprotectVBProj(ByVal Pwd As String)
Dim vbProj As Object
Set vbProj = ThisWorkbook.VBProject
If vbProj.Protection < 1 Then Exit Sub ' already unprotected
Set Application.VBE.ActiveVBProject = vbProj
SendKeys Pwd & "~~"
Application.VBE.CommandBars(1).FindControl(ID:=257 8,
recursive:=True).Execute
End Sub

Sub ProtectVBProj(ByVal Pwd As String)
Dim vbProj As Object
Set vbProj = ThisWorkbook.VBProject
If vbProj.Protection = 1 Then Exit Sub ' already protected
Set Application.VBE.ActiveVBProject = vbProj
SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "{TAB}" & Pwd & "~"
Application.VBE.CommandBars(1).FindControl(ID:=257 8,
recursive:=True).Execute
End Sub

Cheers

--
macropod
[MVP - Microsoft Word]


"dd" <dd.dd wrote in message ...
| Is there a way to enable/disable the VBA password and lock project for
| viewing, using a macro?
|
| Dylan
|
|

macropod,

Thanks very much for providing this macro and answering my question.

I managed to figure out that: & "{Enter}" will close the dialogs. I now find
that the macro runs on the workbook where it is saved (personal macros
workbook, which is hidden) rather than the workbook I'm working on.

Is there any way to apply it to all open workbooks, or, maybe to select from
a list of workbooks?

Regards
Dylan

"dd" <dd.dd wrote in message ...
Hi macropod

macropod, your original version probably works fine, but due to my lack of
knowledge, I don't know how to run it !!

I want to assign it to a custom menu button, but can't see the macro in the
list of available macros.

If I remove the (ByVal Pwd As String), add Dim Pwd As String, and change
Sendkeys to include the confirm password. When I now run it, the Dialog box
remains on the screen.

How do I use the original code?
How do I okay the dialog?

Sub ProtectVBProj() ' to allow it to appear as a macro
'Sub protectVBProj(ByVal Pwd As String) 'not sure how to call this as a
macro?
Dim Pwd As String
Dim vbProj As Object
Set vbProj = ThisWorkbook.VBProject
If vbProj.Protection = 1 Then Exit Sub ' already protected
Set Application.VBE.ActiveVBProject = vbProj
SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "12345" & "{TAB}" & Pwd & "12345"
'However, the dialog remains on the screen - how do I update it.
Application.VBE.CommandBars(1).FindControl(ID:=257 8,
recursive:=True).Execute
End Sub


"macropod" wrote in message
...
Hi Dylan,

Assuming you know the password, the following code, based on work by fellow
MVP Bill Manville, will unprotect/protect a vba project:

Sub UnprotectVBProj(ByVal Pwd As String)
Dim vbProj As Object
Set vbProj = ThisWorkbook.VBProject
If vbProj.Protection < 1 Then Exit Sub ' already unprotected
Set Application.VBE.ActiveVBProject = vbProj
SendKeys Pwd & "~~"
Application.VBE.CommandBars(1).FindControl(ID:=257 8,
recursive:=True).Execute
End Sub

Sub ProtectVBProj(ByVal Pwd As String)
Dim vbProj As Object
Set vbProj = ThisWorkbook.VBProject
If vbProj.Protection = 1 Then Exit Sub ' already protected
Set Application.VBE.ActiveVBProject = vbProj
SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "{TAB}" & Pwd & "~"
Application.VBE.CommandBars(1).FindControl(ID:=257 8,
recursive:=True).Execute
End Sub

Cheers

--
macropod
[MVP - Microsoft Word]


"dd" <dd.dd wrote in message ...
| Is there a way to enable/disable the VBA password and lock project for
| viewing, using a macro?
|
| Dylan
|
|

Hi Dylan,

To do run the code against a different workbook, or a set of workbooks, you'd
add an additional argument to the code, as follows, then call the routines
with both the workbook names and passwords.

Sub UnprotectVBProject(WkBk As Workbook, ByVal Pwd As String)
Dim vbProj As Object
Set vbProj = WkBk.VBProject
If vbProj.Protection < 1 Then Exit Sub ' already unprotected
Set Application.VBE.ActiveVBProject = vbProj
SendKeys Pwd & "~~"
Application.VBE.CommandBars(1).FindControl(ID:=257 8, recursive:=True).Execute
End Sub

Sub ProtectVBProject(WkBk As Workbook, ByVal Pwd As String)
Dim vbProj As Object
Set vbProj = WkBk.VBProject
If vbProj.Protection = 1 Then Exit Sub ' already protected
Set Application.VBE.ActiveVBProject = vbProj
SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "{TAB}" & Pwd & "~"
Application.VBE.CommandBars(1).FindControl(ID:=257 8, recursive:=True).Execute
End Sub

Cheers

--
macropod
[MVP - Microsoft Word]


"dd" <dd.dd wrote in message ...
| macropod,
|
| Thanks very much for providing this macro and answering my question.
|
| I managed to figure out that: & "{Enter}" will close the dialogs. I now find
| that the macro runs on the workbook where it is saved (personal macros
| workbook, which is hidden) rather than the workbook I'm working on.
|
| Is there any way to apply it to all open workbooks, or, maybe to select from
| a list of workbooks?
|
| Regards
| Dylan
|
| "dd" <dd.dd wrote in message ...
| Hi macropod
|
| macropod, your original version probably works fine, but due to my lack of
| knowledge, I don't know how to run it !!
|
| I want to assign it to a custom menu button, but can't see the macro in the
| list of available macros.
|
| If I remove the (ByVal Pwd As String), add Dim Pwd As String, and change
| Sendkeys to include the confirm password. When I now run it, the Dialog box
| remains on the screen.
|
| How do I use the original code?
| How do I okay the dialog?
|
| Sub ProtectVBProj() ' to allow it to appear as a macro
| 'Sub protectVBProj(ByVal Pwd As String) 'not sure how to call this as a
| macro?
| Dim Pwd As String
| Dim vbProj As Object
| Set vbProj = ThisWorkbook.VBProject
| If vbProj.Protection = 1 Then Exit Sub ' already protected
| Set Application.VBE.ActiveVBProject = vbProj
| SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "12345" & "{TAB}" & Pwd & "12345"
| 'However, the dialog remains on the screen - how do I update it.
| Application.VBE.CommandBars(1).FindControl(ID:=257 8,
| recursive:=True).Execute
| End Sub
|
|
| "macropod" wrote in message
| ...
| Hi Dylan,
|
| Assuming you know the password, the following code, based on work by fellow
| MVP Bill Manville, will unprotect/protect a vba project:
|
| Sub UnprotectVBProj(ByVal Pwd As String)
| Dim vbProj As Object
| Set vbProj = ThisWorkbook.VBProject
| If vbProj.Protection < 1 Then Exit Sub ' already unprotected
| Set Application.VBE.ActiveVBProject = vbProj
| SendKeys Pwd & "~~"
| Application.VBE.CommandBars(1).FindControl(ID:=257 8,
| recursive:=True).Execute
| End Sub
|
| Sub ProtectVBProj(ByVal Pwd As String)
| Dim vbProj As Object
| Set vbProj = ThisWorkbook.VBProject
| If vbProj.Protection = 1 Then Exit Sub ' already protected
| Set Application.VBE.ActiveVBProject = vbProj
| SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "{TAB}" & Pwd & "~"
| Application.VBE.CommandBars(1).FindControl(ID:=257 8,
| recursive:=True).Execute
| End Sub
|
| Cheers
|
| --
| macropod
| [MVP - Microsoft Word]
|
|
| "dd" <dd.dd wrote in message ...
| | Is there a way to enable/disable the VBA password and lock project for
| | viewing, using a macro?
| |
| | Dylan
| |
| |
|
|
|
|

Hi Macropod,

I still can't see it in my macros list.

Can you give me an example of how to call it?

Much obliged
Dylan

"macropod" wrote in message
...
Hi Dylan,

To do run the code against a different workbook, or a set of workbooks,
you'd
add an additional argument to the code, as follows, then call the routines
with both the workbook names and passwords.

Sub UnprotectVBProject(WkBk As Workbook, ByVal Pwd As String)
Dim vbProj As Object
Set vbProj = WkBk.VBProject
If vbProj.Protection < 1 Then Exit Sub ' already unprotected
Set Application.VBE.ActiveVBProject = vbProj
SendKeys Pwd & "~~"
Application.VBE.CommandBars(1).FindControl(ID:=257 8,
recursive:=True).Execute
End Sub

Sub ProtectVBProject(WkBk As Workbook, ByVal Pwd As String)
Dim vbProj As Object
Set vbProj = WkBk.VBProject
If vbProj.Protection = 1 Then Exit Sub ' already protected
Set Application.VBE.ActiveVBProject = vbProj
SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "{TAB}" & Pwd & "~"
Application.VBE.CommandBars(1).FindControl(ID:=257 8,
recursive:=True).Execute
End Sub

Cheers

--
macropod
[MVP - Microsoft Word]


"dd" <dd.dd wrote in message ...
| macropod,
|
| Thanks very much for providing this macro and answering my question.
|
| I managed to figure out that: & "{Enter}" will close the dialogs. I now
find
| that the macro runs on the workbook where it is saved (personal macros
| workbook, which is hidden) rather than the workbook I'm working on.
|
| Is there any way to apply it to all open workbooks, or, maybe to select
from
| a list of workbooks?
|
| Regards
| Dylan
|
| "dd" <dd.dd wrote in message
...
| Hi macropod
|
| macropod, your original version probably works fine, but due to my lack of
| knowledge, I don't know how to run it !!
|
| I want to assign it to a custom menu button, but can't see the macro in
the
| list of available macros.
|
| If I remove the (ByVal Pwd As String), add Dim Pwd As String, and change
| Sendkeys to include the confirm password. When I now run it, the Dialog
box
| remains on the screen.
|
| How do I use the original code?
| How do I okay the dialog?
|
| Sub ProtectVBProj() ' to allow it to appear as a macro
| 'Sub protectVBProj(ByVal Pwd As String) 'not sure how to call this as a
| macro?
| Dim Pwd As String
| Dim vbProj As Object
| Set vbProj = ThisWorkbook.VBProject
| If vbProj.Protection = 1 Then Exit Sub ' already protected
| Set Application.VBE.ActiveVBProject = vbProj
| SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "12345" & "{TAB}" & Pwd &
"12345"
| 'However, the dialog remains on the screen - how do I update it.
| Application.VBE.CommandBars(1).FindControl(ID:=257 8,
| recursive:=True).Execute
| End Sub
|
|
| "macropod" wrote in message
| ...
| Hi Dylan,
|
| Assuming you know the password, the following code, based on work by
fellow
| MVP Bill Manville, will unprotect/protect a vba project:
|
| Sub UnprotectVBProj(ByVal Pwd As String)
| Dim vbProj As Object
| Set vbProj = ThisWorkbook.VBProject
| If vbProj.Protection < 1 Then Exit Sub ' already unprotected
| Set Application.VBE.ActiveVBProject = vbProj
| SendKeys Pwd & "~~"
| Application.VBE.CommandBars(1).FindControl(ID:=257 8,
| recursive:=True).Execute
| End Sub
|
| Sub ProtectVBProj(ByVal Pwd As String)
| Dim vbProj As Object
| Set vbProj = ThisWorkbook.VBProject
| If vbProj.Protection = 1 Then Exit Sub ' already protected
| Set Application.VBE.ActiveVBProject = vbProj
| SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "{TAB}" & Pwd & "~"
| Application.VBE.CommandBars(1).FindControl(ID:=257 8,
| recursive:=True).Execute
| End Sub
|
| Cheers
|
| --
| macropod
| [MVP - Microsoft Word]
|
|
| "dd" <dd.dd wrote in message
...
| | Is there a way to enable/disable the VBA password and lock project for
| | viewing, using a macro?
| |
| | Dylan
| |
| |
|
|
|
|

hi Dylan,

You can't see the macros because they're called with arguments.

The following routines call the macros I've already posted to
protect/unprotect all VBA projects (including 'Personal.xls' if it exists - so
be careful) using the same password - change "Password" to suit. I've added
another routine, named 'Delay' to insert a 1-second delay in the loop;
otherwise it tends to run too fast and fall over ...

Sub ProtectOpenVBAProjects()
Dim i As Integer
For i = 1 To Workbooks.Count
ProtectVBProject Workbooks(Workbooks(i).Name), "Password"
Pause 1
Next
End Sub

Sub UnProtectOpenVBAProjects()
Dim i As Integer
For i = 1 To Workbooks.Count
UnprotectVBProject Workbooks(Workbooks(i).Name), "Password"
Pause 1
Next
End Sub

Sub Pause(ByVal Delay As Integer)
Dim newHour, newMinute, newSecond As Integer
Dim waitTime As Date
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + Delay
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
End Sub

The following code can be used to call the same macros to protect/unprotect a
single project:

Sub ProtectOpenVBAProject()
ProtectVBProject Workbooks(InputBox("Please input the Excel filename") &
".xls"), _
InputBox("Please input the VBA Project Password")
End Sub

Sub UnProtectOpenVBAProject()
UnprotectVBProject Workbooks(InputBox("Please input the Excel filename") &
".xls"), _
InputBox("Please input the VBA Project Password")
End Sub

Note that this code asks for both the filename (without the extension) and the
password.

Cheers

--
macropod
[MVP - Microsoft Word]


"dd" <dd.dd wrote in message ...
| Hi Macropod,
|
| I still can't see it in my macros list.
|
| Can you give me an example of how to call it?
|
| Much obliged
| Dylan
|
| "macropod" wrote in message
| ...
| Hi Dylan,
|
| To do run the code against a different workbook, or a set of workbooks,
| you'd
| add an additional argument to the code, as follows, then call the routines
| with both the workbook names and passwords.
|
| Sub UnprotectVBProject(WkBk As Workbook, ByVal Pwd As String)
| Dim vbProj As Object
| Set vbProj = WkBk.VBProject
| If vbProj.Protection < 1 Then Exit Sub ' already unprotected
| Set Application.VBE.ActiveVBProject = vbProj
| SendKeys Pwd & "~~"
| Application.VBE.CommandBars(1).FindControl(ID:=257 8,
| recursive:=True).Execute
| End Sub
|
| Sub ProtectVBProject(WkBk As Workbook, ByVal Pwd As String)
| Dim vbProj As Object
| Set vbProj = WkBk.VBProject
| If vbProj.Protection = 1 Then Exit Sub ' already protected
| Set Application.VBE.ActiveVBProject = vbProj
| SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "{TAB}" & Pwd & "~"
| Application.VBE.CommandBars(1).FindControl(ID:=257 8,
| recursive:=True).Execute
| End Sub
|
| Cheers
|
| --
| macropod
| [MVP - Microsoft Word]
|
|
| "dd" <dd.dd wrote in message ...
| | macropod,
| |
| | Thanks very much for providing this macro and answering my question.
| |
| | I managed to figure out that: & "{Enter}" will close the dialogs. I now
| find
| | that the macro runs on the workbook where it is saved (personal macros
| | workbook, which is hidden) rather than the workbook I'm working on.
| |
| | Is there any way to apply it to all open workbooks, or, maybe to select
| from
| | a list of workbooks?
| |
| | Regards
| | Dylan
| |
| | "dd" <dd.dd wrote in message
| ...
| | Hi macropod
| |
| | macropod, your original version probably works fine, but due to my lack of
| | knowledge, I don't know how to run it !!
| |
| | I want to assign it to a custom menu button, but can't see the macro in
| the
| | list of available macros.
| |
| | If I remove the (ByVal Pwd As String), add Dim Pwd As String, and change
| | Sendkeys to include the confirm password. When I now run it, the Dialog
| box
| | remains on the screen.
| |
| | How do I use the original code?
| | How do I okay the dialog?
| |
| | Sub ProtectVBProj() ' to allow it to appear as a macro
| | 'Sub protectVBProj(ByVal Pwd As String) 'not sure how to call this as a
| | macro?
| | Dim Pwd As String
| | Dim vbProj As Object
| | Set vbProj = ThisWorkbook.VBProject
| | If vbProj.Protection = 1 Then Exit Sub ' already protected
| | Set Application.VBE.ActiveVBProject = vbProj
| | SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "12345" & "{TAB}" & Pwd &
| "12345"
| | 'However, the dialog remains on the screen - how do I update it.
| | Application.VBE.CommandBars(1).FindControl(ID:=257 8,
| | recursive:=True).Execute
| | End Sub
| |
| |
| | "macropod" wrote in message
| | ...
| | Hi Dylan,
| |
| | Assuming you know the password, the following code, based on work by
| fellow
| | MVP Bill Manville, will unprotect/protect a vba project:
| |
| | Sub UnprotectVBProj(ByVal Pwd As String)
| | Dim vbProj As Object
| | Set vbProj = ThisWorkbook.VBProject
| | If vbProj.Protection < 1 Then Exit Sub ' already unprotected
| | Set Application.VBE.ActiveVBProject = vbProj
| | SendKeys Pwd & "~~"
| | Application.VBE.CommandBars(1).FindControl(ID:=257 8,
| | recursive:=True).Execute
| | End Sub
| |
| | Sub ProtectVBProj(ByVal Pwd As String)
| | Dim vbProj As Object
| | Set vbProj = ThisWorkbook.VBProject
| | If vbProj.Protection = 1 Then Exit Sub ' already protected
| | Set Application.VBE.ActiveVBProject = vbProj
| | SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Pwd & "{TAB}" & Pwd & "~"
| | Application.VBE.CommandBars(1).FindControl(ID:=257 8,
| | recursive:=True).Execute
| | End Sub
| |
| | Cheers
| |
| | --
| | macropod
| | [MVP - Microsoft Word]
| |
| |
| | "dd" <dd.dd wrote in message
| ...
| | | Is there a way to enable/disable the VBA password and lock project for
| | | viewing, using a macro?
| | |
| | | Dylan
| | |
| | |
| |
| |
| |
| |
|
|
|

Hi, don't mean to hijack this thread...but I needed something similar and am running into a problem with the code.

I used it as detailed with minor adjustments for password and and the & "{Enter}" as described in the thread. I managed to get it working by itself from within VB and via macro button in Excel...but when I try to call the macro into another macro...it doesn't work. The VBProject remains locked and it won't let me add the code I want to add without putting the password in again.

It even works if I have it as two seperate macro buttons....one to unlock the project and the next to enter the code.

I tried inserting Pause's but that didn't help. I'm at a loss. Any help would be greatly appreciated.

Terry

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

Actually, I now have figured something else out. If I stop the macro right after it does its thing...everything works fine now. However, if the macro continues on to the end...it now causes excel to shut down. It has to be related to this activity because if I manually unlock the target VBProject and then run the macro without the new code....it works fine.

the problem comes in that this activeity is inside an If_Then_Else and Excel bombs out when it tries to do the "End If" statement following all the changes. When Excel recovers and re-opens the files...the are both correct and have the changes in them that the Macro was trying to accomplish.

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

Okay, here's what I've found out since yesterday:
Excel crashes when I try to add a click event to run the new macro I imported from another file. This is all being done via VB so what you see is me copying a button, changing the caption, then adding code to the worksheet for the click event associated with the new button:

Sub MAJORtoPowerPoint_Button()
Windows.Application.Workbooks(2).Activate
Sheets("Instructions").Select
ActiveSheet.Unprotect "PASSWORD"
'copy an existing button
ActiveSheet.Shapes("CommandButton3").Select
Selection.Copy
'create the new button
ActiveSheet.Paste
'Move it where I want it
Selection.ShapeRange.IncrementLeft 175
Selection.ShapeRange.IncrementTop -12
'Change the caption
Selection.Object.Caption = "Major to PowerPoint"
'Unprotect the VB Code
UnprotectVBProj
Pause 3
'insert new click event
ActiveWorkbook.VBProject.VBComponents(ActiveWorkbo ok.ActiveSheet.CodeName).CodeModule.AddFromString _
("Private Sub CommandButton6_Click()" & vbCr & "MAJORtoPowerPoint" & vbCr & "End Sub")
'Protect the worksheet
ActiveSheet.Protect "PASSWORD", DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

The UnprotectVBProj called above looks like this:
Sub UnprotectVBProj()
Dim Pwd As String
Dim vbProj As Object
Set vbProj = ActiveWorkbook.VBProject
If vbProj.Protection < 1 Then Exit Sub ' already unprotected
Set Application.VBE.ActiveVBProject = vbProj
SendKeys Pwd & "PASSWORD" & "{Enter}" & "{Enter}"
Application.VBE.CommandBars(1).FindControl(ID:=257 8, recursive:=True).Execute
SendKeys "{Enter}"
End Sub

I'm thinkning it's a compiler problem, like the worksheet VB codepane doesn't recognize the new macro "MAJORtoPowerPoint" being called as part of the code being added.

Any help would be greatly appreciated.
Terry

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com