Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating/manipulating form controls placed on a spreadsheet in excel using vba
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.Comma ndButton.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").Code Module 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 wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating/manipulating form controls placed on a spreadsheet inexcel using vba
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.Comma ndButton.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").Code Module LineNum = .CreateEventProc("Click", "MyButton") .InsertLines LineNum + 1, "Msgbox ""Hello World""" End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
saving a spreadsheet with form controls | Excel Worksheet Functions | |||
Form controls in Excel 07 | Excel Discussion (Misc queries) | |||
Creating controls to autofill a form | Excel Discussion (Misc queries) | |||
ActiveX Controls vs Form Controls | Excel Discussion (Misc queries) | |||
Label, Text and Form controls in Excel 97 | Excel Programming |