?
Solved

VBA to detect if project is protected

Posted on 2005-03-24
6
Medium Priority
?
688 Views
Last Modified: 2007-12-19
Hi,

   * sorry if this is in the wrong section but I can't find a VBA channel *


   Using VBA in Excel,

     I want a macro that detects whether or not the VBA project is protected.
     The Macro should be contained in the workbook that contains the project to be protected (I think that makes sense but I have have a beer or two)

     If it isn't protected then the macro should protect the project with a specified password.

     I want to make sure (I'm very forgetfull!) that my project is protected even if I have a sudden unexpected short deadline and I forget to protect the code.

Cheers



       
0
Comment
Question by:janoxley
6 Comments
 
LVL 23

Expert Comment

by:gecko_au2003
ID: 13627019
Make a pointer question from this section to this question here :) :

http://www.experts-exchange.com/Applications/MS_Office/Excel/
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 13627228
This is a difficult one

I've used SendKeys before to protect a project but its not 100% reliable

This code should be run from the main excel screen, it will lock the project with the password contained in Mypass - ie Fred

Const MyPass = "Fred"

Sub locktest()
    On Error Resume Next
    SendKeys "%{F11}"    'open vbe
    SendKeys "%te"
    SendKeys "^{PGUP}"
    SendKeys "%v"        'Lock project turned on
    SendKeys "{TAB}"
    SendKeys MyPass      'password
    SendKeys "{TAB}"
    SendKeys MyPass      'password confirmation
    SendKeys "{TAB}"
    SendKeys "{ENTER}"
    SendKeys "%{F11}"     'back to excel
End Sub

Cheers

Dave
0
 
LVL 35

Accepted Solution

by:
mvidas earned 2000 total points
ID: 13630141
Hi Jan,

Here is another method, note that this cannot be initiated from within vba (meaning cannot press F5 from vba to start it).  Similar to Dave's, uses the SendKeys.  I noted the question that I took the original code from, slightly modified to add VBA Ext. reference from within code (which I got thanks to Dave :) ), and a few other minor changes:

Option Explicit
'Modified from Ivan Moala - http://www.experts-exchange.com/Q_20643945.html
Const BreakIt As String = "%{F11}%TE+{TAB}{RIGHT}%V{+}{TAB}"
Sub Change_VBA_PW()
 Dim WB As Workbook, Password As String
 Set WB = ThisWorkbook 'workbook to be changed
 'Adds VBA Extensibility reference
 WB.VBProject.References.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 5, 3
 If WB.VBProject.Protection = 1 Then Exit Sub
 Password = "ivan"
 Call SetVBProjectPassword(WB, Password)
End Sub
Sub SetVBProjectPassword(WB As Workbook, ByVal Password As String)
 Dim VBP As VBProject, WindowVBIDE As VBIDE.Window, i As Long
 Set VBP = WB.VBProject
 Application.ScreenUpdating = False
'// close any code windows to ensure we are in the right project
 For Each WindowVBIDE In VBP.VBE.Windows
  If InStr(WindowVBIDE.Caption, "(") > 0 Then WindowVBIDE.Close
 Next WindowVBIDE
 WB.Activate
 SendKeys BreakIt & Password & "{tab}" & Password & "~" & "%{F11}~", True
 WB.Activate
 SendKeys "%{F11}", True
 Application.ScreenUpdating = True
End Sub

Matt
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:janoxley
ID: 13630597
Hi Matt,

   I put the code in a module and run it via a command button on the worksheet.
 
   I get this error,

    "Programmic Access to Visual Basic Project is not trusted"

any ideas?
0
 
LVL 35

Expert Comment

by:mvidas
ID: 13631896
Hi Jan,

Sorry for the delay, I've had a couple other things to do in the meantime.  If you're getting that error, you must have excel 2002 or greater, as the security changed in 2002.  To be able to manipulate the vba project line this, you'll have to change the security setting.  Go to Tools, then Macros, then Security.  Click the "Trusted Sources" tab, and check the "Trust access to visual basic project" checkbox.  That should get you past the error.  Let me know if anything else comes up, I should be around for the next few hours

Matt
0
 

Author Comment

by:janoxley
ID: 13688168
Cheers Matt,

      That works great now.

0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month8 days, 11 hours left to enroll

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question