Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Success in unprotecting VBA module using VBA
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.Imp ort 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for success... corny I know | Excel Worksheet Functions | |||
Not a lottery success with Excel | Excel Worksheet Functions | |||
Determining football success.. | Excel Discussion (Misc queries) | |||
No Success with GetSaveAs | Excel Discussion (Misc queries) | |||
No success deleting modules | Excel Programming |