PDA

View Full Version : How can one unlock a VBA project (and it's modules) using vba



Jesse.Burns
02-19-2010, 09:07 AM
Hello,

I am having trouble unprotecting a vba project using vba code. I've read that this can be done using SendKeys, however I am unfamiliar with this method and it was not explained in the forum in which I found it.

I have a workbook in which I have protected the associated vba project to prevent manipulation by the end-user(s). Eventually, this workbook will be sent back in to me, and I will then run a bit of code that is stored in a module in my personal project that will need access to a module in the workbook project. Is there any way to unlock the workbook project using vba coding rather than manually unlocking the project each time a workbook is sent back in to me.

I don't know if I have explained this well enough or given enough specific information for my situation. Please let me know if I need to clarify anything.

Thank you for you help!

Bob Phillips
02-19-2010, 09:35 AM
SendKeys is the only way that I know of, but it is problemmatical becuase it depends upon sending a number of return key codes to navigate to your project before sending the password. IF the project could be in a diferent ordinal position on different occasions, it isn't going to work.

Jesse.Burns
02-19-2010, 09:55 AM
Thank you for the quick reply. I know from what I previously read, and what you just reinstated, that SendKeys isn't a very reliable method. It appears that I'll likely need to manually unlock the project before I run the macro. This isn't a huge issue, it would just be ideal to skip this step since I will not be the only one going through this process.

If anyone else has any ideas I'd love to hear them.

Thank you.

Kenneth Hobs
02-19-2010, 04:28 PM
Naturally, xld is correct.

Something like this might work for you. The referenced links and comments explain that you need to set a reference to the Visual Basic for Applications Extensibility Library and Trust access to Visual Basic Project (Excel menu, not VBE, Tools > Macro > Security > Trusted Publishers).
'Posted by Ivan F Moala on September 06, 2001 9:34 PM, http://www.mrexcel.com/archive/VBA/29825.html (http://www.mrexcel.com/archive/VBA/29825.html)
'See Chip Pearson's note about trusting VBAProjects, http://www.cpearson.com/excel/vbe.aspx (http://www.cpearson.com/excel/vbe.aspx)
Option Explicit

Const BreakIt As String = "%{F11}%TE+{TAB}{RIGHT}%V{+}{TAB}"

Sub Change_VBA_PW()
Dim WB As Workbook
Dim Password As String

Set WB = ActiveWorkbook
Password = "test"
Call SetVBProjectPassword(WB, Password)

End Sub

Sub SetVBProjectPassword(WB As Workbook, ByVal Password As String)
'Needs reference to Visual Basic for Applications Extensibility Library
Dim VBP As VBProject
Dim OpenWin As VBIDE.Window
Dim i As Integer

Set VBP = WB.VBProject

Application.ScreenUpdating = False

' close any code windows to ensure we are in the right project
For Each OpenWin In VBP.VBE.Windows
If InStr(OpenWin.Caption, "(") > 0 Then OpenWin.Close
Next OpenWin

WB.Activate

'Application.OnKey "%{F11}"
SendKeys BreakIt & Password & "{tab}" & Password & "~" & "%{F11}~", True
'SendKeys "enter", True
Application.ScreenUpdating = True
WB.Activate
SendKeys "%{F11}", True
End Sub

GTO
02-19-2010, 04:50 PM
Hi Kenneth

Nice to see you about :-) :ole:

Mark

macropod
02-20-2010, 01:03 AM
Hi Jesse,

Try:
Sub UnprotectVBProj(ByVal Pwd As String)
Dim vbProj As Object
Set vbProj = ThisWorkbook.VBProject
If vbProj.Protection <> 1 Then Exit Sub ' already unprotected
Set Application.VBE.ActiveVBProject = vbProj
SendKeys Pwd & "~~"
Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute
End Sub

Jesse.Burns
02-21-2010, 09:15 PM
Thank you all for your replies.

Using SendKeys I've been able to accomplish my task. I will definitely look into adding some of the ideas posted here.

I will definitely apply the idea of closing all open windows, as that will definitely be an issue for this process in the future. My only concern is that this does not appear to close open windows in my PERSONAL.XLS project. Should this be an issue? Is there a way to reference a personal project so as to close any open windows?

Thank you.