I need help with programmatically unlocking my project

Background story
I have a shared excel file, that I am constantly changing the code in.
At one point, I am uploading the code to an online database.
Another user who is using the same file can be notified of the new updates and will be prompted to download and apply them.
Everything works perfectly as long as the project is unlocked for the other user, but this is not the case because the other user is not an Admin and needs to have the project protected.
So I have to unlock the project programmatically in order to apply the code.
I know I cannot re-lock the project but that is ok because I am forcing the workbook to close and the user has to restart it again with the new updated code.

Code that I am using to unlock the project:

Option Explicit

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Public Function unlockVBAProject()
With ThisWorkbook
With .VBProject.VBE.MainWindow
.visible = True
.SetFocus
SendKeys "%t": Sleep 50
SendKeys "e": Sleep 50
SendKeys "^{TAB}": Sleep 50
' SendKeys "%v": Sleep 50
SendKeys "{TAB}": Sleep 50
SendKeys gadminPassword: Sleep 50
SendKeys "{TAB}": Sleep 50
SendKeys gadminPassword: Sleep 50
SendKeys "{TAB}": Sleep 50
SendKeys "~": Sleep 50

End With
End With
End Function

When I test this and the project is unlocked, it works perfectly. I see the Project properties popup and it immediately closes afterwards.
But if I test it while the project is locked and before downloading the code update, the Project properties pops up and then I have to click OK to close it.
I tried adding DoEvents before the first end with, and the program crashes, then I added it before the second end with and the same thing happens.

I know sendkeys is not reliable but honestly I just want something that can work, if you have a different way thats ok.
Also I noticed everytime this code runs the numlock is switched off, do you know why is that?

Thanks a lot!

Hi there,

"How do I get my Developer changes into the User's workbook?"

I have seen variations of this question asked SO many times!

if you have a different way thats ok.

If User data are stored within an application workbook, this problem (or equivalent) can/will arise as soon as the User enters data into the workbook.

The proper way to structure an application is to store User data in a separate (absolutely "vanilla") workbook. When the application workbook is opened, the data are retrieved from the data workbook - when the application workbook is closed, the updated User data are passed to the data workbook which is then saved.

This approach allows the Developer to modify a workbook and/or code whenever required and simply to post the updated application workbook to a shared location. A User can then download the latest version knowing that the latest User data will be imported automatically.

Sorry if the above is not the quick fix you were hoping for, but maybe it's food for thought?

Regards,

Greg M

I understand your answer, but my workbook contains a LOT of data and many sheets, it is not feasible to move the data back and forth.

Thanks Greg for your reply anyway!

I have posted 4 times on this forum and this is the first someone replies
I usually figure out the solution on my own lol