[RESOLVED] Unlocking a project with VBA

[mikeyc1204]

I have an Excel project distributed to several hundred users that is self updating. When launched, it compares it's version number to a current version number on a remote server. If the numbers don't match, all of the modules and forms in the project are replaced with the most up to date versions.

My problem now is that the project needs to be locked down due to some users editing code to circumvent certain controls. If I lock the project for editing or viewing, my modules can't be changed during one of the automatic update.

I can view the current locked state of the project with the following property:
Application.VBE.ActiveVBProject.Protection

Unfortunately the property is read-only.

Is there a way with VBA to unlock a password protected project, allow the necessary changes to occur and then relock it?

[RobDog888]

I do not believe its possible and even if it was you would have to have each and every user manually check the "Trust programmatic access to VBA Projects" as this is also not programmable either.

[JustinLabenne]

Well if you know the password and can supply it: (Note: this is not a finished code, it still needs a bit of work, but with a correctly supplied password, works pretty good even with send keys) It does include a bit of a test for the "Trusted Access to Visual Basic" problem also, as RobDog pointed out.

Option Explicit
'Your password goes here!!!!
Const gszProjPassword As String = "hello"
 
Public Sub UnlockMe()
    Dim wbName As Variant
    Dim wbBook As Workbook
    Dim vbaProj As Object
    Dim oWin As Object
    Dim X As Integer
 
    On Error GoTo ErrorHandler
 
    'Select the workbook with the project to unlock
    wbName = Application.GetOpenFilename("Excel Files (*.xls),*.xls")
    
    
    'Open it, assign an object ref to it's vba project
    Set wbBook = Workbooks.Open(wbName)
    Set vbaProj = wbBook.VBProject
    
    'Close any open code windows
    For Each oWin In vbaProj.VBE.Windows
      If InStr(oWin.Caption, "(") > 0 Then oWin.Close
    Next oWin
    
    Application.VBE.MainWindow.Visible = False
    
    'Check to see if the VBA project is already unlocked
    If vbaProj.Protection <> 1 Then
        MsgBox "The VBA Project for the file you selected is already unlocked.", 0
        Exit Sub
        
    'We found the project to be locked
    ElseIf vbaProj.Protection = 1 Then
 
        On Error Resume Next
        
            Do While X < 4
                If vbaProj.Protection <> 1 Then
                    MsgBox "The VBA project for " & wbName & " was unprotected successfully", 64
                    Exit Do
                End If
                UnprotectVBProject wbBook, gszProjPassword
                X = X + 1
            Loop
        On Error GoTo 0
 
    End If
    
ErrorExit:
    Set wbBook = Nothing
    Set vbaProj = Nothing
    Exit Sub
    
ErrorHandler:
    Select Case Err.Number
         
    Case 1004
    
        wbBook.Close False
        
        MsgBox "You will need to set the " & _
        "{ TRUST ACCESS TO VISUAL BASIC PROJECT } setting" & vbNewLine & _
        "When the dialog appears, go to the Trusted Sources tab, " & _
        "check the setting, click OK, and rerun this code again", 64
        SendKeys "%T", True
        SendKeys "M", True
        SendKeys "S", True
         
    Case Else
         
        MsgBox Err.Description
         
    End Select
    Resume ErrorExit
End Sub
 
Public Sub UnprotectVBProject(wb As Workbook, ByVal Password As String)
    Dim vbProj As Object
 
    On Error GoTo ErrorHandler
 
    Application.ScreenUpdating = False
    
        Set vbProj = wb.VBProject
 
        'Check to see if VBA project is already unlocked
        If vbProj.Protection <> 1 Then Exit Sub
    
        'Activate chosen VBA Project
        Set Application.VBE.ActiveVBProject = vbProj
 
            'SendKeys is the only way
            If Password = "^^" Or Password = "++" Then
                Password = ""
                Exit Sub
            ElseIf Right(Password, 2) = "^^" Or Right(Password, 2) = "++" Then
                Password = ""
                Exit Sub
            Else
                SendKeys Password & "~~" & "{ESC}"
                Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute
            End If
    
        'Not the right password
        If vbProj.Protection = 1 Then
            SendKeys "%{F11}", True
        End If
 
        'Reset Password
        Password = ""
    
    Application.ScreenUpdating = True
    Set vbProj = Nothing
    
    Exit Sub
 
ErrorHandler:
    MsgBox Err.Description, 64
End Sub

[RobDog888]

I was going to suggest something like sendkays but I hate sendkeys as its so unreliable. If the user accidentally hits a key or clicks the mouse it will throw it all out of sync.

I would reccomend writting your app as an Add-In or as a dll so there would be a minimum of code exposed in the VBA IDE. Its allot easier to update a dll file vs the alternative.

[JustinLabenne]

Agreed. One other crazy thing I had seen in regards to utilizing my method above was that "why not disable the keyboard while it's running?".

Never bothered to try and implement that kind of thing into this, if your really needing to protect your code that much from users, dll or locked up add-in all the way.... just replace the add-in with an updated add-in, so much easier....and reliable.

[RobDog888]

I think we are both thinking the same things tonight! Great minds think alike.

[mikeyc1204]

You're both my heroes. I have the sendkeys option in there for now, but I'd love to get everything in a dll. What do you use to compile your vba code to a dll?

[mikeyc1204]

Nevermind. The internet concensus seems to be VB6 while avoiding VB.NET at all costs for dlls. Thanks again.

[RobDog888]

If you dont have VB6 you may want to look into MS Office Developer version which ccan make dlls seeing how your doing commercial support and production.