?
Solved

VBA to detect if project is protected

Posted on 2005-03-24
6
Medium Priority
?
651 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month10 days, 20 hours left to enroll

770 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