Programmatically modifying code using VBE in a protected project

[Alex van der Spek]

I am programmatically changing code lines to allow users to modify a
modelfunction in Excel:

So I use the VBE like:


ThisWorkbook.VBProject.VBComponents("Maincode").CodeModule.ReplaceLine 6,
CodeLine

Does anyone know how to do this and still lock the project for viewing
using a password?

Thanks!
Alex van der Spek

 

[Barb Reinhardt]

I'm not aware that you can programmatically lock a project. Think about it,
that could be a very slippery slope if locked projects can be unlocked
programmatically.

 

[Jim Thomlinson]

Coding against the VBE and password protection do not get along. Your only
option involves using send keys which is hit and miss... Check out this
link...

http://www.cpearson.com/excel/vbe.aspx
Specifically
The VBA Project that you are going to change with these procedures must be
unlocked. There is no programmatic way to unlock a VBA project (other than
using SendKeys). If the project is locked, you must manually unlock.
Otherwise, the procedures will not work.

 

[Barb Reinhardt]

Hi Jim,

I've not used Send Keys and would prefer not to, but why is it's use
problematic?

Thanks,
Barb Reinhardt

 

[Alex van der Spek]

I had not thought of using SendKeys myself. That should work, but it is
cumbersome indeed.

How about splitting the code in two parts:

1. One locked project stored in an Add-in. This project contains the main
code including the bit that manipulates the VBE.
2. One unlocked file, the user front end. This project contains the one
line of code that should be modified at the user's wish (updating a model
function). The modification, however is done by the locked project which
contains the VBE manipulation code.

Would that work?

Thanks!
Alex van der Spek