How to create a button and then rename it using a Macro

  • I have created various macros to run on various worksheets. I want to add a button in the selected worksheet after running the relevant macro and rename it to my preferred name. What I did is.... I recorded a macro to create a button using insert contro - button and then renamed it to my preferred name and saved the macro.. For the first time, the button is name "Button 1". Then when i rerun the macro it gives an error coz, the macro looks for the button named "Button 1" which is not available. The button is automatically named as "Button 2". Can you please help me how to fix this.

    If the explanation is unclear, please let me know, i will explain my objective what i exactly want to do. Thanks.

  • Re: How to create a button and then rename it using a Macro


    Thanks mate for your code. It works but not as per what i need. I went on google and made some more interesting stuff and it almost meets what i need but still trying to achieve my requirement 100%. Here is the code:

    Code
    1. Sub CreateButton()
    2. ActiveSheet.Buttons.Add(50, 1, 61, 30).Select
    3. Selection.Name = "New Button"
    4. Selection.OnAction = "Macro name"
    5. ActiveSheet.Shapes("New Button").Select
    6. Selection.Characters.Text = "New Name"
    7. End Sub
  • Re: How to create a button and then rename it using a Macro


    Per the Forum Rules, all VBA code must be wrapped in code tags, which you omitted. I have added them for you. Please take the time read and understand the rules, and avoid any infractions that may (will) affect your posting privileges.


    You don't need to make a selection to work with the button.


    Code
    1. Sub CreateButton()
    2. With ActiveSheet.Buttons.Add(50, 1, 61, 30)
    3. .Name = "New Button"
    4. .OnAction = "Macro name"
    5. .Characters.Text = "New Name"
    6. End With
    7. End Sub


    Also, what exactly is your requirement @ 100%?

  • Re: How to create a button and then rename it using a Macro


    Thanks. Actually, i have developed 5 macros for different reports for which i have diff. worksheets where the data needs to be copied and then run the macro. Also, I one sheet for main menu with all the macros name provided. Then the user will select the report that needs to be generated. Then i will hide all the other worksheets and unhide only the one that is selected by the user (but this will not run the macro immediately). Then the user will copy the data in this worksheet. After the data is copied, he will have 2 buttons, 1. Back to Main Menu 2. Run Macro. Option 1 will just hide all the sheets & unhide the main menu sheet. Option 2 will run the macro. So when i do this thru vba code, i cud do it for the first time. When i do it for the 2nd time, since there is already both the buttons exists, it creates an error. I want to see if i can tune this and make it work. Any thots?

  • Re: How to create a button and then rename it using a Macro


  • Thank you so much, that work well;):thumbup: