+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    jeff.naifeh@gmail.com
    Guest

    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. #2
    Chip Pearson
    Guest

    Re: 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.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


    <jeff.naifeh@gmail.com> wrote in message
    news:1123879484.796102.80760@f14g2000cwb.googlegroups.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. #3
    Jon Peltier
    Guest

    Re: 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.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
    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1