Add Controls at Runtime with Events

  • Thread starter Thread starter llowwelll
  • Start date Start date
L

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!
 
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
 
Back
Top