Insert code into worksheets programatically??

[Caro-Kann Defence]

Hi.

I have the following procedure that runs after a bunch of worksheets are
added to a workbook. When the user selects a particular cell on one of the
added worksheets the program evaluates whether a userform should be displayed
to prompt to some information.

The problem I have is that while this procedure runs fine on my machine,
when I distribute this to other users, the code is not added. Is there
something I am missing? As far as I can see, all the references are correct
(though I am not positive on this).

Sub Test

Set dBook = ActiveWorkbook

On Error Resume Next
Application.EnableEvents = False
Application.DisplayAlerts = False

For Each vbc In dBook.VBProject.VBComponents
If Left(vbc.Name, 5) = "Sheet" Then
If vbc.CodeModule.CountOfLines = 0 Then
vbc.CodeModule.InsertLines 1, "Private Sub
Worksheet_SelectionChange(ByVal Target As Range)"
vbc.CodeModule.InsertLines 2, "Dim rng as Range"
vbc.CodeModule.InsertLines 5, " Set rng = Target"
vbc.CodeModule.InsertLines 6, " Call CheckTarget(rng)
"
vbc.CodeModule.InsertLines 10, "End Sub"
End If
End If
Next vbc

Application.EnableEvents = True
Application.DisplayAlerts = True

On Error GoTo 0

End Sub

 

[Dave Peterson]

I have tools|options|editor tab|require variable declaration checked (inside the
VBE).

When your code runs, it looks for 0 lines in those modules. xl2003 seems to add
"option explicit" to the module before your code runs.

And a second guess, any chance that the activeworkbook's project is protected?

 

[Caro-Kann Defence]

Hi Dave.

Thanks for the response. I never considered Excel versions having that kind
of impact. I am using xl2000 and the people I am distributing to are using
xl2003. I need to delve further into that.

I do require variable declaration as well. I Dim'ed the variables outside of
the procedures.

The application is not protected (at least not yet!).

I've tried changing the code first count the number of lines already in the
module and then adding the code below on the next available line.

 

[Tom Ogilvy]

The application is not protected (at least not yet!).
If you lock the code in your project, you won't be inserting code lines in
it.

Perhaps you would need to create a dummy workbook, create the sheets there,
add the code, then copy them into your workbook and delete the dummy - I
don't know if that works with a locked project or not as I haven't tried it.