Re: Programmatically adding code to ThisWorkbook
Pardon me for butting in, but this thread looks like a convenient place for the issue I am having myself.
Basically I have an xlsm file which fetches data from an external source (ADO) and uses copyrecordsettorange (or somesuch) to push it into a worksheet.
There are actually around 20 different sheets for various data sources.
Having loaded the data, the code cycles through the rows and depending on particular values add navigation buttons to the worksheet to allow the user to drill down / across / up etc.
The code also generates on_click events for each button and sticks it in the worksheet's code module.
This is all working hunky-dory, but then I want to protect my code (especially the main-line) and that is when the fun starts.
Setting the VBProject 'lock code for viewing' apparently stops programmatic manipulation of the code (not fair, because I am not 'viewing' I am writing code).
I have trawled half-a-dozen sites looking for ways to programmatically remove the 'VBproject lock' (in the same way as I remove the 'Workbook protection' and reinstate it afterwards every time I un-hide a new worksheet).
I have seen that the only available option is the 'sendkeys' solution (which is in itself complicated because the key strokes need to be different the first time - existing password required - and the subsequent times).
But fundamentally I suspect that although I can remove the 'viewing lock', I cannot put it back on at the end of the response.
Does anyone have any more guidance to give ? (other than don't do it, which is too obvious). If anyone is interested, I can post sections of the code to illustrate what is working (like adding code using code) and not working (like removing VBProject lock).