Success in unprotecting VBA module using VBA

[michael.beckinsale]

Hi All,

Further to numerous posts about the above subject l have pasted below
the code l have used succesfully to unprotect a protected VBA module
(password known) in another workbook (Target.xls), then import a VBA
module (STP.bas) then run the imported macro (testone). The idea is to
update some 50+ workbooks using an control type workbook which is
(Operational.xls) in this example.
Obviously the code needs a lot of refinement to meet my exact
requirements but hopefully should form a good basis for anyone who
needs to do similar.

I would be grateful if the experts out there could review the code to
see if it can be improved / made more robust. I am aware that the use
of SendKeys is frowned upon but is the only known method of
unprotecting VBA modules.

Many thanks must go to Bill Manville who provided the function to
unlock the VBA Module in a post he made to this newsgroup in JAN 2000.

CODE IN Operational.xls

Sub UnprotectVBProject(WB As Workbook, ByVal Password As String)
'Function to unlock VBA Project if password is known

Dim VBP As VBProject, oWin As VBIDE.Window
Dim wbActive As Workbook
Dim i As Integer
Set VBP = WB.VBProject
Set wbActive = ActiveWorkbook

If VBP.Protection <> vbext_pp_locked Then Exit Sub

Application.ScreenUpdating = True

'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 "%{F11}%TE" & Password & "~~%{F11}", True

'Check to see if project unlocked
If VBP.Protection = vbext_pp_locked Then
' if failed - maybe wrong password
SendKeys "%{F11}%TE", True
End If

' Leave no evidence of the password
Password = ""

' Go back to the previously active workbook
wbActive.Activate

End Sub

Sub UnprotectProjectandImportVBAmodule()
'Use function to unprotect VBA Project
UnprotectVBProject Workbooks("Target.xls"), "password"
'Import required VBA Module to target workbook
Workbooks("Target.xls").VBProject.VBComponents.Import
Filename:="X:\Development\STP.bas"
'Activate target workbook
Workbooks("Target.xls").Activate
'Run macro residing in target workbook
Run "Target.xls!testone"
End Sub



CODE IN Target.xls

Sub testone()

MsgBox ("You have unlocked the project, imported the module called
STP.bas, and run the intended macro")

End Sub

Hope the above is of use to someone.

Regards

Michael beckinsale