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/

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

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