Don't open vb editor
 

I am using a macro to write a macro in the ThisWorkbook Module, but when
it runs it opens the vb editor. I don't want this to happen, so does
anyone know a way to stop this from happening?

Here is my current code:


Code:
--------------------
Sub test()

If ProcedureExists("Workbook_SheetChange", "ThisWorkbook") Then
DeleteProcedure ("Workbook_SheetChange")
End If

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
StartLine = .CreateEventProc("SheetChange", "Workbook") + 1
.InsertLines StartLine + 1, _
"If Target = Sheet1.Range(""A1"") Then" + vbCrLf _
+ "Sheet2.Range(""A1"") = Target.Value" + vbCrLf _
+ "ElseIf Target = Sheet2.Range(""A1"") Then" + vbCrLf _
+ "Sheet1.Range(""A1"") = Target.Value" + vbCrLf + _
"End If" + vbCrLf
End With

End Sub

Function ProcedureExists(ProcedureName As String, _
ModuleName As String) As Boolean
On Error Resume Next
If ModuleExists(ModuleName) = True Then
ProcedureExists = ThisWorkbook.VBProject.VBComponents(ModuleName) _
.CodeModule.ProcStartLine(ProcedureName, vbext_pk_Proc) < 0
End If
End Function

Function ModuleExists(ModuleName As String) As Boolean
On Error Resume Next
ModuleExists = Len(ThisWorkbook.VBProject.VBComponents(ModuleName ).Name) < 0
End Function

Sub DeleteProcedure(ProcedureName As String)

Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long

Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("ThisWorkbook" ).CodeModule
With VBCodeMod
StartLine = .ProcStartLine(ProcedureName, vbext_pk_Proc)
HowManyLines = .ProcCountLines(ProcedureName, vbext_pk_Proc)
.DeleteLines StartLine, HowManyLines
End With

End Sub
--------------------



John Vickers


--
John Vickers
------------------------------------------------------------------------
John Vickers's Profile: http://www.excelforum.com/member.php...o&userid=31551
View this thread: http://www.excelforum.com/showthread...hreadid=513276

Don't open vb editor
 

Vvery clever code, I have to spend the next ten years at university to
decipher it before I can respond.

However, you have a dim statement mid way in a sub routine. VB does not
approve of this. Put your Dim statement at the top. Dim StartLine As Long

If you could tell "us" what it is suppose to then maybe "we" could provide
alternative code.


http://www.geocities.com/excelmarksway




"John Vickers" wrote:


I am using a macro to write a macro in the ThisWorkbook Module, but when
it runs it opens the vb editor. I don't want this to happen, so does
anyone know a way to stop this from happening?

Here is my current code:


Code:
--------------------
Sub test()

If ProcedureExists("Workbook_SheetChange", "ThisWorkbook") Then
DeleteProcedure ("Workbook_SheetChange")
End If

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
StartLine = .CreateEventProc("SheetChange", "Workbook") + 1
.InsertLines StartLine + 1, _
"If Target = Sheet1.Range(""A1"") Then" + vbCrLf _
+ "Sheet2.Range(""A1"") = Target.Value" + vbCrLf _
+ "ElseIf Target = Sheet2.Range(""A1"") Then" + vbCrLf _
+ "Sheet1.Range(""A1"") = Target.Value" + vbCrLf + _
"End If" + vbCrLf
End With

End Sub

Function ProcedureExists(ProcedureName As String, _
ModuleName As String) As Boolean
On Error Resume Next
If ModuleExists(ModuleName) = True Then
ProcedureExists = ThisWorkbook.VBProject.VBComponents(ModuleName) _
.CodeModule.ProcStartLine(ProcedureName, vbext_pk_Proc) < 0
End If
End Function

Function ModuleExists(ModuleName As String) As Boolean
On Error Resume Next
ModuleExists = Len(ThisWorkbook.VBProject.VBComponents(ModuleName ).Name) < 0
End Function

Sub DeleteProcedure(ProcedureName As String)

Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long

Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("ThisWorkbook" ).CodeModule
With VBCodeMod
StartLine = .ProcStartLine(ProcedureName, vbext_pk_Proc)
HowManyLines = .ProcCountLines(ProcedureName, vbext_pk_Proc)
.DeleteLines StartLine, HowManyLines
End With

End Sub
--------------------



John Vickers


--
John Vickers
------------------------------------------------------------------------
John Vickers's Profile: http://www.excelforum.com/member.php...o&userid=31551
View this thread: http://www.excelforum.com/showthread...hreadid=513276