1

When my VB project finishes running, it saves a new copy of the excel workbook, and then e-mails this to a provided distribution. When the code is run, the VB project should be unprotected, allowing any error thrown by the VBE to be "debugged" (which is not possible for locked projects - unfortunately the reasons for debugging cannot be accounted for within the error handler; it's mainly relating to a query which runs against a SQL server database).

However, I wish for the VB project to be protected once it's saved down as the new copy, preventing anyone who receives the distributed copy being able to access the VB project.

I've had a search, and couldn't seem to find any answer relating to locking a VB project using VBA. Any guidance is appreciated.

Community
  • 1
  • 1
luke_t
  • 2,935
  • 4
  • 22
  • 38
  • Why not cut the routine up to saving a new copy then protect it manually and email it afterwards. I'm assuming we're only talking about 1 file. – L42 Jun 30 '15 at 09:44
  • It's an option.. if it is possible to automate though, the lazy side in me would rather that. – luke_t Jun 30 '15 at 09:59
  • http://www.ozgrid.com/forum/showthread.php?t=13006&p=65776#post65776 – Maciej Los Jun 30 '15 at 10:41

1 Answers1

2

Based on this thread:

Sub ProtectVBProject(WB As Workbook, ByVal Password As String) 

    Dim VBP As VBProject, oWin As VBIDE.Window 
    Dim wbActive As Workbook 
    Dim i As Integer 

    Set VBP = WB.VBProject 
    Set wbActive = ActiveWorkbook 

    ' Close any code windows To ensure we hit the right project 
    For Each oWin In VBP.VBE.Windows 
        If InStr(oWin.Caption, "(") > 0 Then oWin.Close 
    Next oWin 

    WB.Activate 
    ' now use lovely SendKeys To unprotect 
    Application.OnKey "%{F11}" 
    SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Password & "{TAB}" & Password & "~" 
    Application.VBE.CommandBars(1).FindControl(Id:=2578, recursive:=True).Execute 
    WB.Save 
End Sub
Maciej Los
  • 8,468
  • 1
  • 20
  • 35
  • Thanks Maciej.. from reading the thread I have noted that it's absolutely not a good idea to use send keys. I have chosen not to go ahead and use this solution, however I will accept your answer as it appears to be the only solution available (although with some risk). Thanks! – luke_t Jun 30 '15 at 11:35
  • 1
    Have you looked into using Windows API? http://stackoverflow.com/questions/30508341/unlocking-password-protected-vba-project/30630037#30630037 – findwindow Jun 30 '15 at 19:19