Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jul 2006
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Recording a macro using a macro (All)

    Is it possible to have a Sub or a Function call that starts the recording of a new macro?
    Essentially what I would like to do is have a button on my sheet that does "Tools>Macro>Record New Macro" automatically without having to go through the menu
    Ideally - it would also use a predefined macro name.

    Any assistance would be great. Thanks.
    Regards, Brenton

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Recording a macro using a macro (All)

    Hi bgarth,

    You could simply add the 'record new macro' button to one of your existing menus. You can do this via Tools|Customise|Commands > Tools, then selecting the 'record new macro' button and dragging to wherever you want. That doesn't automatically give you your own pre-defined name, but it's a lot faster than going through the menus every time.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Lounger
    Join Date
    Jul 2006
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recording a macro using a macro (All)

    Thanks for that Paul, but what I actually want to try and do is save the newly recorded macro into a different workbook
    I thought I would create a button that opened the workbook for recording macros then start recording and jump back to the original document
    This was I could lock the code down in my original workbook but still allow macros to be recorded
    ANy thoughts?

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Recording a macro using a macro (All)

    Hi bgarth,

    If you add an Auto_Open macro to your Personal.xls workbook, you could code that macro to open your macros workbook every time Excel starts. That way the macros workbook would always be available and you could select it as the destination workbook via the button referred to in my previous post.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    Lounger
    Join Date
    Jul 2006
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recording a macro using a macro (All)

    Thanks for that Paul. I never noticed that destination workbook options before. I will use that - thanks.
    But what would be really great is if I could open that window from a button and prefill the macro name and the destination - so the user only had to click start

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Recording a macro using a macro (All)

    For hopefully obvious reason, there is no VBA instruction for starting the macro recorder. But you can cheat by executing the corresponding menu item. And you can pre-fill items by using SendKeys, although that is notoriously flaky, so your mileage may vary. Example:

    Sub Test()
    SendKeys "MyMacro{TAB}{TAB}{UP}{TAB}{DEL}"
    Application.CommandBars.FindControl(ID:=184).Execu te
    End Sub

Posting Permissions

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