Results 1 to 8 of 8

Thread: [RESOLVED] Adding a Command Button programatically in Excel 2010 (VBA)

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2010
    Location
    Wirral, UK
    Posts
    7

    Resolved [RESOLVED] Adding a Command Button programatically in Excel 2010 (VBA)

    I need to start with an alert. I'm returning to VBA after an absence of a couple of years so don't be surprised if it looks like I'm doing something more than usually dopey .

    What I'm trying to do is add a command button containing a call to a macro from its click event onto a spreadsheet (not a userform) on Sheet1. This is to be called via the workbook's open event.

    I found something roughly applicable on the internet but it's giving catch-all code 1004 with message 'Cannot insert object'. This is the code, which I've put in a module called from workbook_open (I haven't put the macro call in yet, for simplicity. I'm concentrating on getting the button just to appear):

    Code:
    Public Sub CreateButtonTry01()
    
    Dim objObject As Object
    Dim strCode As String
    Sheets("Sheet1").Select
    Set objObject = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
    Link:=False, DisplayAsIcon:=False, Left:=200, Top:=100, Width:=100, _
    Height:=35)
    objObject.Name = "Try01"
    ActiveSheet.OLEObjects(1).Object.Caption = "Try"
    
    End Sub
    Debug is highlighting the three lines of the 'Set objObject' statement (red).

    Note: the parameters in the highlighted statement are as copied from the internet source. What I really want is to have the button in a cell (location B20) and automatically stretching horizontally and vertically to exactly fill the cell. If anyone can suggest a refinement to achieve this it would be a great bonus.

    Triturus.

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,570

    Re: Adding a Command Button programatically in Excel 2010 (VBA)

    Try something like (note that I used D5, just for grins):

    Code:
    Sub addButton()
        Dim objBtn As Object
        Dim ws As Worksheet
        Dim celLeft As Integer
        Dim celTop As Integer
        Dim celWidth As Integer
        Dim celHeight As Integer
        
        Set ws = ActiveSheet
        celLeft = ws.Range("d5").Left
        celTop = ws.Range("d5").Top
        celWidth = ws.Range("d5").Width
        celHeight = ws.Range("d5").Height
        
        Set objBtn = ws.OLEObjects.Add(ClassType:="Forms.CommandButton.1", link:=False, _
            displayasicon:=False, Left:=celLeft, Top:=celTop, Width:=celWidth, Height:=celHeight)
    End Sub

  3. #3

    Thread Starter
    New Member
    Join Date
    Oct 2010
    Location
    Wirral, UK
    Posts
    7

    Re: Adding a Command Button programatically in Excel 2010 (VBA)

    Same error as before I'm afraid vbfbryce, with debug highlighting the Set statement as with my code

  4. #4
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,570

    Re: Adding a Command Button programatically in Excel 2010 (VBA)

    Interesting. Creates a button for me just fine.

    Are you running it from an Auto_Open, or no?

    EDIT: Nevermind, I see you said "Workbook_open."

  5. #5
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,570

    Re: Adding a Command Button programatically in Excel 2010 (VBA)

    I called it from Workbook_Open, and it works there also. (2013)

  6. #6

    Thread Starter
    New Member
    Join Date
    Oct 2010
    Location
    Wirral, UK
    Posts
    7

    Re: Adding a Command Button programatically in Excel 2010 (VBA)

    Curious....must be something environmental...or me doing something unclever. I will have a careful look at everything....

  7. #7
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: Adding a Command Button programatically in Excel 2010 (VBA)

    Quote Originally Posted by Triturus View Post
    Curious....must be something environmental...or me doing something unclever. I will have a careful look at everything....
    Can you insert a button at all from the Developer Tab->Controls->Insert->ActiveX menu?

    If not, you are probably suffering from December's Update snafu. see: http://excelmatters.com/2014/12/10/o...ivex-controls/

  8. #8

    Thread Starter
    New Member
    Join Date
    Oct 2010
    Location
    Wirral, UK
    Posts
    7

    Re: Adding a Command Button programatically in Excel 2010 (VBA)

    Quote Originally Posted by TnTinMN View Post
    Can you insert a button at all from the Developer Tab->Controls->Insert->ActiveX menu?

    If not, you are probably suffering from December's Update snafu. see: http://excelmatters.com/2014/12/10/o...ivex-controls/
    That's it! I should have known it would be Microsoft

    As we said in the old days, 'Thanks Bill'.

    Your tailored button solution now works a treat vbfbryce.

Posting Permissions

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



Click Here to Expand Forum to Full Width