Vba To Insert Command Button

ojchase · Apr 26th 2007

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?

btadams · Apr 26th 2007

Re: Vba To Insert Command Button

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

Code

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
    Range("H12").Select
End Sub

Display More

ojchase · Apr 27th 2007

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?

Vba To Insert Command Button

Participate now!

Share