Programmatically Adding Code to a Workbook

[Chaplain Doug]

Excel 2003. As I create new workbooks, I wish to place the following code
into the workbook:

Private Sub Workbook_Open()
ThisWorkbook.Sheets(1).EnableSelection = xlUnlockedCells
End Sub

How can I do this programmatically?

The reason I need to do this is because as I create the new workbook, I am
copying in a single sheet (Sheets(1)) which is protected and which has only
certin cells unlocked. I only want the user to be able to select the
unlocked cells. Although the EnableSelection=xlUnlockedCells is set in the
original, it does not carry over on the copy. Thus, the need for the
Workbook_Open routine to reset it. Thanks for the help.

 

[Tom Ogilvy]

http://www.cpearson.com/excel/vbe.htm

has all the info. Note that the security setting must allow programmatic
access to the VBE.

Tools=Macro=>Security then Trusted Publishers tab. checkbox at the bottom

 

[Ron de Bruin]

For the OP

If you want to check this setting see John's site
http://www.j-walk.com/ss/excel/tips/tip96.htm

 

[NickHK]

Include it in a template.xlt and open that.
Or edit the default workbook that is opened upon New. It called Book.xls, I
think.
There's also the default worksheet in WB called Sheet.xls or something.
These 2 option will affect ALL new WBs, so it may not be a good idea.

But this will have no effect intially on the copied sheet, only the next
time the file is opened.
It will however set .EnableSelection = xlUnlockedCells on the sheet that is
about (2).

NickHK

 

[Chaplain Doug]

Thanks Tom. But I had a problem. I added this code:

With Wb.VBProject.VBComponents("ThisWorkbook").CodeModule
StartLine = .CreateEventProc("Open", "Workbook") + 1
.InsertLines StartLine, _
"ThisWorkbook.Sheets(1).EnableSelection = xlUnlockedCells"
End With

When it executes the .CreateEventProc it adds the event procedure and
crashes Excel. Any thoughts?

 

[Chaplain Doug]

I also tried:
Set VBCodeMod = Wb.VBProject.VBComponents("ThisWorkBook").CodeModule
With VBCodeMod
StartLine = .CountOfLines + 1
.InsertLines StartLine, _
"Private Sub Workbook_Open()" & Chr(13) & _
" ThisWorkbook.Sheets(1).EnableSelection = xlUnlockedCells" &
Chr(13) & _
"End Sub"
End With
And got the same results, code added to workbook, but Excel crashed.

 

[Chaplain Doug]

Due to the attempts at programmatically adding code crashing Excel, I simply
tried:

Wb.Sheets(1).EnableSelection = xlUnlockedCells

AFTER the sheet had been copied into Wb. This works. Problem solved.
Thanks!

 

[Tom Ogilvy]

have you tried saving, then closing the new workbook, then reopening it? Is
the restriction still in effect?