Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 3 of 3

Thread: Vba To Insert Command Button

  1. #1
    Join Date
    5th November 2006

    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?

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    27th January 2003
    Richmond, VA

    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 

    Excel Video Tutorials / Excel Dashboards Reports

  3. #3
    Join Date
    5th November 2006

    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?

    Excel Video Tutorials / Excel Dashboards Reports

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Run Command Button Click From Another Command Button
    By ExcelJunkie in forum Excel General
    Replies: 11
    Last Post: January 2nd, 2014, 00:02
  2. Macro Works With Form Button But Not Command Button
    By cwins in forum Excel General
    Replies: 2
    Last Post: October 5th, 2006, 15:47
  3. Insert Date With Command Button
    By kiask2343 in forum Excel and/or Access Help
    Replies: 2
    Last Post: September 23rd, 2006, 00:04
  4. Replies: 6
    Last Post: January 13th, 2006, 19:11
  5. Replies: 1
    Last Post: October 23rd, 2004, 11:13


Posting Permissions

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