I am a module in an xla to add a command button to a sheet and set the
click event. When I get to the wsLogSheet.CodeName line, it tells me
index out of range. The help says that should be a long and not a
string so I tried using wsLogSheet.Index. The line runs without an
error but my click code is getting added to a module in the xla that is
adding the worksheet instead of getting added to the worksheet. And
then the button doesn't do anything. How do I get the code into the
active worksheet?


Set wsLogSheet = ActiveWorkbook.Worksheets.Add
Set oleo = .OLEObjects.Add(ClassType:="Forms.CommandButton.1" , _
Link:=False, DisplayAsIcon:=False, Left:=378,
Top:=2, _
width:=70, Height:=20)
oleo.Name = "LogSheetAction"
With
ThisWorkbook.VBProject.VBComponents(wsLogSheet.Cod eName).CodeModule
Dim newline As Long
Dim newproc As String

newline = .CountOfLines + 1
newproc = "Sub LogSheetAction_Click()" & vbCrLf &
vbCrLf
newproc = newproc & vbTab & "ShowUpdaterForm" & vbCrLf
& vbCrLf
newproc = newproc & "End Sub"
.InsertLines newline, newproc
End With