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").CodeModule
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.CodeName).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).Name) <> 0
End Functio
