aniemann
May 5th, 2006, 01:15 PM
I'm using Excel 2003 SP2.
The premise is I add worksheets at run-time based off of user actions. The sheets then add command buttons and then assign some vb code to be fired when clicked. Everything works great except the VB editor pops up (even when its closed). This is annoying, any way to stop it from doing so?
Public Sub Test(myWS As Excel.Worksheet)
Dim OLEObj As OLEObject
Dim Rng As Range
Dim WS As Worksheet
Dim CodeMod As Object
Dim LineNum As Long
Set WS = myWS
Set Rng = Range("I13")
Set OLEObj = _
WS.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Top:=Rng.Top, Left:=Rng.Left, Height:=Rng.Height * 2, _
Width:=Rng.Width * 2)
OLEObj.name = "test"
OLEObj.Object.Caption = "test"
Set CodeMod = _
ThisWorkbook.VBProject.VBComponents(WS.CodeName).CodeModule
LineNum = CodeMod.CreateEventProc("Click", OLEObj.name)
CodeMod.insertLines LineNum + 1, _
"msgbox(""test"")"
End Sub
The premise is I add worksheets at run-time based off of user actions. The sheets then add command buttons and then assign some vb code to be fired when clicked. Everything works great except the VB editor pops up (even when its closed). This is annoying, any way to stop it from doing so?
Public Sub Test(myWS As Excel.Worksheet)
Dim OLEObj As OLEObject
Dim Rng As Range
Dim WS As Worksheet
Dim CodeMod As Object
Dim LineNum As Long
Set WS = myWS
Set Rng = Range("I13")
Set OLEObj = _
WS.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Top:=Rng.Top, Left:=Rng.Left, Height:=Rng.Height * 2, _
Width:=Rng.Width * 2)
OLEObj.name = "test"
OLEObj.Object.Caption = "test"
Set CodeMod = _
ThisWorkbook.VBProject.VBComponents(WS.CodeName).CodeModule
LineNum = CodeMod.CreateEventProc("Click", OLEObj.name)
CodeMod.insertLines LineNum + 1, _
"msgbox(""test"")"
End Sub