How can you put 'beforedoubleclick' code into a new worksheet that you add ed

Adam
If I understand you correctly you wish to
add a new sheet WITH the double click event
in this newly created sheet....via code ??

If so then try this code;
Note you will need to reference the
micrsoft visual basics extensibility Object library = vbeext1.olb

The 1st Mod runs Both routines = add sheet & add
module code.......change code as necc.

Ivan

Sub CreateSheet_DblCk_Event()
Add_Sheet
Add_DblClick_To_CodeMod
End Sub

Sub Add_DblClick_To_CodeMod()
Dim ModEvent As CodeModule 'Module to Modified
Dim LineNum As Long 'Line number in module
Dim SubName As String 'Event to change as text
Dim Proc As String 'Procedure string
Dim EndS As String 'End sub string
Dim Ap As String 'Apostrophe
Dim Tabs As String 'Tab
Dim LF As String 'Line feed or carriage return
Dim newShCodeNm 'Newsheet code name
Dim oWks As Worksheet

Set oWks = ThisWorkbook.ActiveSheet
Ap = Chr(34)
Tabs = Chr(9)
LF = Chr(13)
EndS = "End Sub"

'Get Code name of sheet
Set newShCodeNm = oWks.Parent.VBProject.VBComponents(oWks.CodeName).Properties("_CodeName")

'Your Event Procedure OR SubRoutine
SubName = "Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range," _
& "Cancel As Boolean)" & LF

'Your Procedure
Proc = "If Range(" & Ap & "A1" & Ap & " ) = 1 Then" & LF
Proc = Proc & Tabs & "MsgBox " & Ap & "Testing number =" & Ap & "& Range(" & Ap & "A1" & Ap & ")" & LF
Proc = Proc & "End If" & LF

'Use ThisWorkbook so that it cannot act on this workbook
Set ModEvent = ThisWorkbook.VBProject.VBComponents(newShCodeNm).CodeModule
With ModEvent
LineNum = .CountOfLines + 1
.InsertLines LineNum, SubName & Proc & EndS
End With
End Sub

Sub Add_Sheet()

On Error Resume Next
Tryagain:
Sheets.Add
If Err <> 0 Then
Application.DisplayAlerts = False
ActiveSheet.Delete
GoTo Tryagain
End If
On Error GoTo 0

End Sub

I have no doubt Ivans code will work. But as an alternative, have you considered creating a Template Worsheet with the code already written and Inserting this a your Worsheet ?

Dave
OzGrid Business Applications

Ivan,

I dont want to create the new sheet with the doubleclick event. I want the new sheet to have the doubleclick functionality. I want to have this new sheet have this code in it.

Can you help. I am new to this and I dont understand your code ??

My excel crashes everytime i run your code. do you why this would happen ??

thanks
adam

, I dont want to create the new sheet with the doubleclick event. I want the new sheet to have the doubleclick functionality. I want to have this new sheet have this code in it. Can you help. I am new to this and I dont understand your code ??

Did you try mine ?

thanks

OzGrid Business Applications

Re: Did you try mine ?

Dave,

I dont understand what you mean create a template. How would I copy to code over to my sheet.

Re: Did you try mine ?

Adam

Open a new Workbook and delete all but one sheet. In the module of this one sheet put all the code you want, eg DoubleClick etc. Now save the file as a MySheet.xlt

Close the file and now open any Excel file and use this code:

Sheets.Add Type:="C:\Program Files\Microsoft Office\Templates\MySheet.xlt"

Dave
OzGrid Business Applications