Create an addin

  • The attched workbook has a small macro included that will make copies of Sheet2 and rename them based on the names in the Reference tab.


    I'd like to convert this to an add-in. I know how to make this an add-in in terms of changing the properties in the VBA explorer. What I need help with is updating the code to work against any workbook. Currently this will only run within this workbook.


    I don't know where to begin to make this more portable, please help get me started.


    Thanks!

  • Re: Create an addin


    First you will need call to create a button for your macro when the book opens so place this in your woorkbook

    Code
    1. Private Sub Workbook_BeforeClose(Cancel As Boolean)
    2. Call DeleteMenu
    3. End Sub
    4. Private Sub Workbook_Open()
    5. Call CreateMenu
    6. End Sub


    then you will need to make the button and set the action

    Code
    1. Sub Createmenu()
    2. Application.CommandBars("Standard").Controls.Add Type:=msoControlButton, Id _
    3. :=2950, Before:=23
    4. OnAction CopySheets
    5. End Sub


    Then on close you will need to delete the menu

    Code
    1. Application.CommandBars("Standard").Controls.Delete


    The placement would be the standard bar in front of the help button with a :) .
    All untested

  • Re: Create an addin


    I took some code I had tried some stuff.
    After opening look in tools and there should be Copy Sheet.
    Cant take the credit for it and dont remember where I got it from.


    You need to look at you code thou as this will only run for a sehhet named "reference sheet".


    Also if you just need this on your computer instead of install as aan add-in you could just save your macro to your personal file.