+ Reply to Thread
Results 1 to 6 of 6

Macro to Create Button and assign code

  1. #1
    Forum Expert swatsp0p's Avatar
    Join Date
    10-07-2004
    Location
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,545

    Macro to Create Button and assign code

    Good morning gang. I am trying to write a macro that will create a command button on a sheet and assign VBA code to that button. Basically, my original code creates a new sheet. I would like that new sheet to have button on it that will, when clicked, run another macro.

    Here's what I have (of course it doesn't work as intended. The button is created, but the label isn't changed nor is the code assigned):

    Please Login or Register  to view this content.
    Any ideas would be greatly appreciated. Thanks in advance!

    Bruce
    Bruce
    The older I get, the better I used to be.
    USA

  2. #2
    Forum Contributor
    Join Date
    12-12-2005
    Posts
    667
    This is the code to add a button and assign a macro:
    ActiveSheet.Buttons.Add(111, 51.75, 42.75, 13.5).Select
    Selection.OnAction = "Macro1"
    Best regards,

    Ray

  3. #3
    Forum Expert swatsp0p's Avatar
    Join Date
    10-07-2004
    Location
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,545
    Thanks, Ray. That worked a treat. I added the line :
    Selection.Caption = "UPDATE TOC"

    and that names the button correctly.

    I knew I would find a solution here.

    Cheers!

    Bruce

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,366
    Quote Originally Posted by swatsp0p
    Good morning gang. I am trying to write a macro that will create a command button on a sheet and assign VBA code to that button. Basically, my original code creates a new sheet. I would like that new sheet to have button on it that will, when clicked, run another macro.

    Here's what I have (of course it doesn't work as intended. The button is created, but the label isn't changed nor is the code assigned):

    Please Login or Register  to view this content.
    Any ideas would be greatly appreciated. Thanks in advance!

    Bruce
    Hi Bruce,
    Very impressive,
    how can you set the range for the button, say next to the active cell??

  5. #5
    Registered User
    Join Date
    02-05-2014
    Location
    canada
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Macro to Create Button and assign code

    Quote Originally Posted by raypayette View Post
    This is the code to add a button and assign a macro:
    ActiveSheet.Buttons.Add(111, 51.75, 42.75, 13.5).Select
    Selection.OnAction = "Macro1"

    I know I'm bringing this thread back from the dead but I have a slight problem.

    I wrote a macro that does the following:
    Create a button on a new sheet, assign it an onaction macro, then move it to a new workbook where the macro is added through a BAS file. Now unfortunately the onaction does not point to the correct macro. How can I fix it?

    Thanks in advance!

    Maxim

  6. #6
    Registered User
    Join Date
    02-05-2014
    Location
    canada
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Macro to Create Button and assign code

    Quote Originally Posted by davesexcel View Post
    Hi Bruce,
    Very impressive,
    how can you set the range for the button, say next to the active cell??
    I know it's 8 years too late but in case other users are looking for the same answer:

    Please Login or Register  to view this content.
    Last edited by maxim123123; 02-06-2014 at 12:21 PM. Reason: added code tags

+ 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