Don't open vb editor

[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:

Please Login or Register  to view this content.

John Vickers

[exceluserforeman]

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.

[email protected]
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("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
> --------------------
>
>
>
> 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
>
>