Add Controls at Runtime with Events

[llowwelll]

I need to add a series of controls at runtime in Excel that I can interact
with via events. How do I do this?

Specifically, my program automatically queries a database every five
minutes to update it's information. The information is constantly
changing. For each record in the database, the program creates a row of
info on an Excel worksheet. I want to be able to create a command button
and label for each record on the worksheet. How would I accomplish this?

Thanks in advance!

 

[Chip Pearson]

Try something like the following code

Dim OLEObj As OLEObject
Dim Rng As Range
Dim WS As Worksheet
Dim CodeMod As Object
Dim LineNum As Long
Set WS = ActiveSheet
Set Rng = Range("G10")

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 = "MyButton"
OLEObj.Object.Caption = "Click Me"
Set CodeMod =
ThisWorkbook.VBProject.VBComponents(Sheet1.CodeName).CodeModule
LineNum = CodeMod.CreateEventProc("Click", OLEObj.Name)
CodeMod.InsertLines LineNum + 1, _
"Msgbox ""You clicked me"" "

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com