VBA Code dynamically written to worksheets not working when project is protected

[janullrich18]

Hey Guys,

My problem:

I have a userform launched on the workbook open event for the user to input the names and tech data for a range of fans. For example, they enter 5 fans. Once they enter the data into the form and click start, 5 sheets named,for example Fan1, Fan2.... to Fan5 are dynamically created and the user form closes. The start button click sub on the form also involves a part to write code to each worksheet module for a worksheet change event. Everything works fine when the VBproject is not locked, as soon as I lock it and go through the exact same process, I get an error on the

ThisWorkbook.VBProject.VBComponents("Sheet" & IntSheetCount).CodeModule.AddFromString StrWorksheetChangeCode

Any ideas on why this happens?

Cheers

 

[tdh777]

Hi Jan.

I have a pretty good idea oif what might be the problem and actually I am thinking you could work it out too.

Your question is something like "I have a room in my house. Normally I can open the door and walk in to it. Today I locked the door with the key. Now when I try to turn the handle and open the door, I can't. Does anybody know why ?"

After a little reflection, I think you might be able to answer your own question.

Cheers,

Warren K.

 

[janullrich18]

haha, yeah. When you put it like that it sounds like my question is a stupid one. Maybe what I should have asked is "is it impossible to write code to a module when the project is locked, or is there a workaround?"

Hi Jan.

I have a pretty good idea oif what might be the problem and actually I am thinking you could work it out too.

Your question is something like "I have a room in my house. Normally I can open the door and walk in to it. Today I locked the door with the key. Now when I try to turn the handle and open the door, I can't. Does anybody know why ?"

After a little reflection, I think you might be able to answer your own question.

Cheers,

Warren K.