Best way to update macro's?

T

Troy1

Board Regular
Joined
Jul 2, 2014
Messages
149
Hello Guys,

I am trying to figure out the best way to update macros on my coworkers macros. They all have workbooks saved on there computer tailored to there specific terminals they cover. However they all use the same macro's (email macros, formatting macros, etc) embedded into each of there workbooks. Every time I want to update there macros I have to do it about 50 times for each workbook.

I am wondering if there is a way to have peoples macro's to automatically update when I update a master macro file or have the ability to create a new macro that will automatically install on everyone's workbook.

I have done research on this online and people are saying to create an add-in then somehow work it into a template. But I do not understand how this will automatically update there macros or add new macros to there workbooks. They can not create a new workbook everyday they have to use the workbooks they already have because all of there data is in there with tons of formulas, charts, etc.

Can someone please help me out?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
You could put all the share code in and add-in and distribute it to users.

Whenever you need to update a macro make the changes in the add-in and distribute the updated version.
 
Upvote 0
Create an add-in and save it read-only on a network share.

Making it read-only will keep Excel from locking the add-in and enable you to update it as necessary.
 
Upvote 0
Hey guys I am a little confused on making an add-in. I created a new workbook named "Macro Updater" and created a module. Ive pasted one of the updated macros into the "Macro Updater" workbook module (not personal). Then I saved the workbook as an add-in. I then installed the add-in into a new workbook but the macros are not showing (I made sure the add-in was checked). How do I make it to where I install the add-in and all of the macros embedded into the "macro updater" workbook will show on there computers?
 
Upvote 0
Unfortunately, macros in an add-in don't appears in the Macros dialog. You can, however, assign keyboard shortcuts in the add-in, and they will be available to all users.
 
Upvote 0
shg said:
Unfortunately, macros in an add-in don't appears in the Macros dialog. You can, however, assign keyboard shortcuts in the add-in, and they will be available to all users.
Click to expand...

Ok I finally got it to work by adding the macro to the quick access bar. Is there a macro or something I can build into the add-in to create a "Add in" bar and then add the macro to the bar automatically?

I found a macro online but it is not working for me for some reason. It will add the tool bar and the button "Push me!" on the created bar. But when I push the button it keeps saying "Onetwothree (thats the name of the macro I made) does not exist. Please make sure macros are not disabled." It would be nice if the add in could delete or replace the old macro's embedded into that spreadsheet so that way only the fresh macro's are installed.

Here is the code:

Code: 
 Private Sub Workbook_AddinInstall()Dim MyMacro As CommandBarButton
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Push ME!").Delete
Set MyMacro = Application.CommandBars("Worksheet Menu Bar").Controls.Add
With MyMacro
.Caption = "Push ME!"
.Style = msoButtonCaption
.OnAction = "Onetwothree"
End With
Set MyMacro = Nothing
End Sub


Private Sub Workbook_AddinUninstall()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Push ME!").Delete
End Sub
 
Last edited:
Upvote 0
Troy1 said:
Ok I finally got it to work by adding the macro to the quick access bar. Is there a macro or something I can build into the add-in to create a "Add in" bar and then add the macro to the bar automatically?

I found a macro online but it is not working for me for some reason. It will add the tool bar and the button "Push me!" on the created bar. But when I push the button it keeps saying "Onetwothree (thats the name of the macro I made) does not exist. Please make sure macros are not disabled." It would be nice if the add in could delete or replace the old macro's embedded into that spreadsheet so that way only the fresh macro's are installed.

Here is the code:

Code: 
 Private Sub Workbook_AddinInstall()Dim MyMacro As CommandBarButton
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Push ME!").Delete
Set MyMacro = Application.CommandBars("Worksheet Menu Bar").Controls.Add
With MyMacro
.Caption = "Push ME!"
.Style = msoButtonCaption
.OnAction = "Onetwothree"
End With
Set MyMacro = Nothing
End Sub


Private Sub Workbook_AddinUninstall()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Push ME!").Delete
End Sub
Click to expand...


Bump!
 
Upvote 0
Where's OneTwoThree? It needs to be in a standard code module.
 
Upvote 0
Ok here is what I am doing to create the add in from the start.

1. Create a new workbook.
2. In the new workbook I added a new module and pasted a macro I created.
3. In the project explorer I select "Thisworkbook" in the new workbook created and pasted the following code.

Code: 
Private Sub Workbook_AddinInstall()Dim MyMacro As CommandBarButton
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Push ME!").Delete
Set MyMacro = Application.CommandBars("Worksheet Menu Bar").Controls.Add
With MyMacro
.Caption = "Push ME!"
.Style = msoButtonCaption
.OnAction = "Onetwothree"
End With
Set MyMacro = Nothing
End Sub




Private Sub Workbook_AddinUninstall()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Push ME!").Delete
End Sub

4. Exit project explorer and save as excel addin.
5. Create another new workbook to test the install of the addin.
6. Install the add-in into the workbook and ensure it is checked in the add in box.
7. Now there is an "Addin" toolbar created with a "Push Me!" button that is supposed to run my "Onetwothree" macro.
8. Press the "Push Me!" button and get the error "Onetwothree.Onetwothree can not be found in this workbook. Please make sure macros are not disabled."
 
Upvote 0
Not sure I followed all that, but 123 has to be a macro in a standard code module in the add-in.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Custom Ribbon Macros point to old workbook
Replies
4
Views
64
Jeffrey Mahoney
Jeffrey Mahoney
J
  • Solved
VBA Project (Automatic Update) and Macro reference with file name change
Replies
8
Views
312
JDSOuth49
J
C
  • Question
Delete Sheet When Corresponding Row in UserForms is Deleted
Replies
1
Views
228
rpaulson
rpaulson
M
  • Question
Change Font when using xWs.Copy macro
Replies
1
Views
136
Jeffrey Mahoney
Jeffrey Mahoney
C
  • Question
Deleting Form Control in copied destination workbook
Replies
0
Views
168
CMKNIGHT
C

Forum statistics

Threads
1,215,024
Messages
6,122,729
Members
449,093
Latest member
Mnur

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top