No announcement yet.

Vba To Insert Command Button

  • Filter
  • Time
  • Show
Clear All
new posts

  • Vba To Insert Command Button

    Hey. I am using a button on a custom-made form to create a new worksheet. Clicking the button will:

    1. Ask for a name
    2. Make a worksheet using that name
    3. Give worksheet a random color
    4. Insert Name into A1
    5. Bold, and change name to size 24 in A1
    6. Insert a command button into the new worksheet.

    Step #6 is the ONLY one that doesn't work, mostly because I don't know how to do it. Another worksheet has a command button on it already that is attached to a totally different macro. I would like an exact duplicate of that command button on every new page added with this user form. (It can't be a copy and paste code in case I should update the other button in the future.) How can I use VBA code to automatically create a working command button for step 6?

  • #2
    Re: Vba To Insert Command Button

    Here's some code generated with the macro recorder with some minor changes:

    Sub AddButton()
    Dim strBname As String
        ActiveSheet.Buttons.Add(Range("B5").Top, Range("B5").Left, 89.25, 23.25).Select
        strBname = Selection.Name
        Selection.OnAction = "Personal.xls!AddLimitsToChart"
        Selection.Characters.Text = "Add Limits"
        With Selection.Characters(Start:=1, Length:=10).Font
            .Name = "Times New Roman"
            .FontStyle = "Regular"
            .Size = 10
        End With
    End Sub


    • #3

      Re: Vba To Insert Command Button

      That works for adding a button, which I see is attached to the AddLimitsToChart macro you must have created. That is more than I knew how to do; thanks. However, what I really need is not only for the button to be created but for it to be created as an identical copy of the other button on another sheet. Like a clone. It would be nice if it automatically updated itself each time the spreadsheet turned on as well. It can't just be a link; it needs to be a duplicate link. Does that make more sense?