J
John Vickers
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("ThisWorkbook").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
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("ThisWorkbook").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