need help protecting source code..

[wolf.stalker]

hi all and thanks for any help you may be able to give me. let me tell you what i am trying to do...

i have a macro that lives in my workbook. the source code is protected at this level via right click on module, properties, protection...works great for THIS workbook.

my problem is that when i run this macro, it creates a new workbook (that also contains code) but then the code that WAS protected in the orgianl workbook is now not procted in the new one...

is there a way to add in a bit of code to the main workbook that says

"when i run and build a new workbook, i will protect that workbooks source code too?"

thanks for the help.

-wm

[Bob Phillips]

Put your code in an addin and protect that workbook.

[wolf.stalker]

well...that's kinda what it does and what i need the help with?

when the user runs the main macro, it then creates 15 NEW workbooks based off of the 15 sheets in the main workbook. each sheet has it's own bit of code too (hense the need for protection).

i need help on how to protect the new workbooks at the time of creation.

[Bob Phillips]

No, I mean only hold the code in one place, do not replicate it.

[wolf.stalker]

here is what i am up against. someone here (can't find user name...sorry) wrote a macro that take the user's workbook and then create a new workbook for each sheet that was in the orginal. that's basicly what i am doing.

the problem is that each sheet in the parent workbook has code that is attached to it and has to be there...i can "protect" the parent workbook. that's ez.

when the macro is run and the 15 children are produced, their code is NOT protected. I have been trying to find / figure out how to at creation level, protect those 15 children.

i can't post my file here as it's over 2M but here is a perfect example of my file...

my workbook is called STORE. I have 4 sheets (BUILD, STORE 1, STORE 2, STORE 3). on the BUILD sheet, i run a macro that creates 3 new workbooks...one for each store that is then sent to that store. they update the workbooks and send them back to me.

i then open the workbook that was sent back to me and i run a macro that is in THAT workbook that uploads data back into a file for me.

there is more to it than that, but that's the jist of it. i could at creaetion go in and do this all by hand, but this process is going to take place 5 days a week, 52 weeks a year and during hours that i do not work.

[wolf.stalker]

hmm..i kinda figured i would have recieved perhaps a few more responses. as it stands now, my boss says that the information stored on those sheets will "most likely" not be viewed by others as they are probably not aware of it and we can roll with the project "as is".

i just wish i had a way to protect them at any level opposed to just being completely open.

[Bob Phillips]

I'm not an advocate of SendKeys, but maybe you can adjust this to make it set a password.

This code unlocks a protected project so you will need to replicate the steps to set a password, but I'll leave that to you

[vba]
With Application
.SendKeys "%{F11}", True ' VBE
.SendKeys "^r", True ' Set focus to Explorer
.SendKeys "{TAB}", True ' Tab to locked project
.SendKeys "~", True ' Enter
.SendKeys "password"
.SendKeys "~", True ' Enter
End With
[/vba]

[R1C1]

You said that the user updates the workbook and sends it back to you. During the update process, is the macro in the workbook being used by the person updating the workbook?

Alan

[wolf.stalker]

sorry for the long delay...

xld : i will try and play around with the send keys. i have never done anything with them so it should be fun to learn :-)

r1c1 - "During the update process, is the macro in the workbook being used by the person updating the workbook?"

the macro in the workbook is only used by me WHEN it comes back. the end user is not allowed to use it's function.

[R1C1]

If you are the only one that uses the code in the workbook, I don't understand why it's in the workbook. If the code doesn't interact as the user enters information, or if the user can't access any of the macros, why is it there? You can run code from your computer that will do the same thing. Sending a workbook, with unused code in it, to multiple users just increases storage costs.


Alan

[wolf.stalker]

Alan:

I have a master workbook (name=MyWorkBook) that has multiple 15 worksheets(SheetA, SheetB,....SheetO). a user on my end runs A macro that then turns all of those workSHEETS into workBOOKS. she then sends out all of the new workbooks (now called WorkbookA, WorkbookB, ect) to a group of people to fill out and return to us.

on each of the workSHEETS, there is a command button that MY user uses when the workbookA, workbookB, etc comes back (all 15 of them).

this is the code i am refering to. BEFORE anything is created/sent off, all the code is protected. at the point when my MasterWorkbook is split into 15 new workbooks, thats when my code becomes open because it does not keep the same security setting <i guess>.

all the code that i have written serves a purpose and is needed. i can't write a general procedure to do all of it as each sheet is different. i already tried :-)