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

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

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


<[email protected]> wrote in message
news:[email protected]...
> 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
>

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/
_______


Chip Pearson wrote:

> 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
>
>
>