Programmatically Adding Code to a Workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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?
 
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.
 
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!
 
have you tried saving, then closing the new workbook, then reopening it? Is
the restriction still in effect?
 
Back
Top