Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to disable screen updating for the Visual Basic Editor
I have an application that uses the CodeModule object to insert and
delete code from the Visual Basic Editor (VBE). The problem is that when the code is executing, focus switches to the VBE and displays each set of code being inserted into the VBE. This also slows execution of the application. Is there a way to disable screen updating for the VBE? Thanks in advance!! --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to disable screen updating for the Visual Basic Editor
I was thinking about the above post and realized it could probably us
some more detail. Here is the code in question. This code causes th VBE to appear while the code is running and you can see each ne procedure added as it happens. As you can imagine, this significantl slows the running of the program. Any ideas on how to hide or disabl "screen updating" for the VBE while this code is executing? Thanks i advance!! Dim OLEObj As OLEObject Dim WS As Worksheet Dim CodeMod As Object, OldBtns, Btn, D, BtnNum As Integer Dim BtnName As String Dim LineNum As Long Dim VBCodeMod As CodeModule Dim StartLine As Long Dim HowManyLines As Long Set WS = ActiveSheet OldBtns = WS.OLEObjects.Count If OldBtns 2 Then 'I have 2 buttons that I want to keep 'This loop deletes the procedures and buttons that I want _ to delete For D = OldBtns To 1 Step -1 'Thanks to Chip Pearson for _ this line BtnName = WS.OLEObjects(D).Name If Left(BtnName, 6) = "Button" Then 'All of the buttons _ I want to delete are named 'Button' something BtnNum = CInt(Right(BtnName, Len(BtnName) - 6)) If ProcedureExists("Button" & BtnNum & "_Click" "Sheet1") = False Then GoTo line432: 'Check to see if the procedur actually exists before attempting to delete it Set VBCodeMod ThisWorkbook.VBProject.VBComponents.("Sheet1").Cod eModule With VBCodeMod StartLine = .ProcStartLine("Button" & BtnNum "_Click", vbext_pk_Proc) HowManyLines = .ProcCountLines("Button" & BtnNum "_Click", vbext_pk_Proc) .DeleteLines StartLine, HowManyLines 'Deletes th procedure End With line432: WS.OLEObjects(D).Delete 'Deletes the button End If Next D End If Dim MsnNo() As String ReDim MsnNo(1 To Size) As String 'This loop creates an array to be used to give each button its _ caption For X = 1 To Size MsnNo(X) = Worksheets("Missions").Cells(4 + X, 5).Value Next X 'This loop adds the buttons that I want to add For Btn = 1 To Size Set OLEObj WS.OLEObjects.Add(classtype:="Forms.CommandButton. 1", _ Top:=100.25 + ((Btn - 1) * 17.25), Left:=121, Height:=17 Width:=38) OLEObj.Name = "Button" & Btn OLEObj.Object.Caption = MsnNo(Btn) OLEObj.Object.Font.Size = 4 Set CodeMod ThisWorkbook.VBProject.VBComponents(Sheet1.CodeNam e).CodeModule LineNum = CodeMod.CreateEventProc("Click", OLEObj.Name) CodeMod.InsertLines LineNum + 1, "Dim BtnNo as Integer" Chr(13) & "BtnNo=" & Btn & _ Chr(13) & "Call AddMsnSheets(BtnNo)" Next Btn 'These are the functions that are called. THANKS to Chip _ Pearson's web page! 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).Na me) < 0 End Functio -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to disable screen updating for the Visual Basic Editor
llowwelll
Try putting: Sub Your_sub Application.screenupdating=False your code goes here Application.screeupdating = True <<<<This reset screen updating whe code is done. HTH Charle -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening Visual Basic Editor | Excel Discussion (Misc queries) | |||
Visual Basic Editor | Excel Discussion (Misc queries) | |||
Visual Basic Editor opens bi itself | Excel Discussion (Misc queries) | |||
Visual basic editor | Excel Discussion (Misc queries) | |||
How to disable Visual Basic Editor in Excel file | Excel Programming |