creating/manipulating form controls placed on a spreadsheet in excel using vba

[jeff.naifeh]

Hi,
I need to create/access the objects that excel creates when you place a
control on a spreadsheet in excel using the form toolbar. I can't find
a reference to the controls in vba anywhere and the only control
references I have been able to find pertain strictly to "userform1" or
some other variant of a popup form. I need to be able to create a form
directly on the spreadsheet or at least be able to access the controls
I have manually created in excel.
cheers,
-JPN

 

[Chip Pearson]

The following code will create a command button on the active
sheet and create an event procedure for that command button:

Dim OLEObj As OLEObject
Dim LineNum As Long

Set OLEObj =
ActiveSheet.OLEObjects.Add(classtype:="Forms.CommandButton.1")
With OLEObj
.Top = Range("C3").Top
.Left = Range("C3").Left
.Width = Range("C3").Width
.Height = Range("C3").Height
.Name = "MyButton"
With .Object
.Caption = "Click Me"
End With
End With

With ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
LineNum = .CreateEventProc("Click", "MyButton")
.InsertLines LineNum + 1, "Msgbox ""Hello World"""
End With



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

 

[Jon Peltier]

A similar approach to adding the control is AddFormControl:

Sub AddButton()
Dim myButton As Shape
Set myButton = ActiveSheet.Shapes.AddFormControl _
(xlButtonControl, 100, 10, 100, 20)
With myButton
.Name = "My Button"
.TextFrame.Characters.Text = "Click Me"
.OnAction = "MyMacro"
End With
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______