Programmatically unlock VBA Project

[AerosAtar]

In order to run some of the Subs on my new Form Administration Page (specifically, the two sections of code that list all functions in ThisWorkbook and Module1), I need to be able to programmatically unlock the VBA Project (which is protected to prevent users from screwing with the code, and I have been told that this is not optional).

The unlock will only happen when the Form Administration worksheet is initialised, and access to this will be (in the final version) controlled by checking the current user's ADlogin against a list of 'authorised administrators' (which will hopefully minimise unauthorised/accidental access).

I have done a lot of searching on this and most initial responses seem to be "you can't". However, I have also come across two (admittedly quite old) ways that this can, in fact, be done - SENDKEYS and the Windows API.

Having looked into these, I am not willing to risk the uncertainty and unreliability involved in using the SENDKEYS method, so have inserted the API code into Module1 of my workbook (attached). However, while it does unlock the project, it is not behaving entirely as it claims/as desired.

For example: It currently prompts for a password (it is supposed to enter it automatically), and none of the 'debug' MsgBox calls are being initialised. It is also opening the VBA Project Properties window before finally showing an error that the Password window couldn't be found (even though it was manually input earlier).

The code is currently called from a Worksheet_Activate() event, so if you switch to Sheet2, then back to Form Administration, you will see what I mean. Password is "test", in case it is needed.

Does anyone have any ideas where this is falling over/misbehaving?

[AerosAtar]

Ok, having toyed around with this some more, if I leave the original code (from Unprotecting VBA Project Password (Using a password that you know)) exactly as it is, it works. However, as this file will not always be stored in the same place with the same name (the file is a form used to submit information relating to setting something up on our systems), I want to work with the Active Workbook. This seems to be where things start falling apart, though I cannot understand why.

To summarise:
* Creating a new Excel instance and opening a file from a specific local location, works.
* Creating a new Excel instance and attempting to work with the current file, doesn't work.
* Using the current Excel instance and opening a file from a specific local location, doesn't work.
* Using the current Excel instance and attempting to work with the current file, doesn't work.

Does anyone have any ideas, because I am drawing a complete blank.

[ashu1990]

try setting the files into the variables to work on them eg:-

dim wbk as workbook
set wbk = workbooks.open(path)
or set wbk = activeworkbook