Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CSV formatted files open odly in Excel 2000 | Excel Discussion (Misc queries) | |||
i try to open Excel spreadsheets, and they open in wordpad-- | Excel Discussion (Misc queries) | |||
cannot open an xl97 file | Excel Discussion (Misc queries) | |||
How do I find a file/spreadsheet that Excel says is Already open but I can't find it? | Excel Discussion (Misc queries) | |||
When I open excel document it always ask if I want to open it in . | Excel Discussion (Misc queries) |