SCRIPT TO install Macro(VB) to Users EXCEL TOOL BAR

[HotNumbers]

Does anyone know how to install a Macro Button and assign it to a Button on a users EXCEL toolbar.

I have written code that needs to be installed to number of users tools bars. Users are not excel savy need to have it installed and assigned to button.

PLS HELP

 

[GettingThere]

Hi,

This isn't the easiest thing to do but here's a few ideas as a start...

1. I tried recording adding a button and came up with:

    Application.CommandBars("Standard").Controls.Add Type:=msoControlButton, ID:=2950, Before:=9

This will add the smiley face button on the users standard toolbar

2. Installing a macro programatically. The only way I know of doing this is a little longwinded, depending on the size of your macro:

Sub Add_Macro()
Set VBP = ThisWorkbook.VBProject
Set VBModule = VBP.VBComponents.Item("ThisWorkbook").Codemodule
'All of the code of the macro you want to add goes here...
VBModule.AddFromString ("Sub Added_Macro" & vbCrLf & "msgbox (""Hello World!"") " & vbCrLf & "End Sub")
End Sub

- Note that for VBA to add code to itself the user needs to go to Tools - Macro - Security - Trusted Sources and 'Trust Access to Visual Basic Project'

Combining these two will allow you to send a sheet to all your users - they click a button to start it off and then it will add the button and install the macro. The two points I can't advise on yet are:

1. How to assign the macro to the button, and
2. Whether the 'add macro' macro can be used to add itself to the personal macro folder.

I'm going to do some research on this because I'm actually interested in finding it out too so if I work any more out I'll let you know!! lol

Hope that helps.
A.

 

[RoryA]

Your best bet is to distribute the code as an add-in that the users can install. At the simplest level, you just need code like this in the ThisWorkbook module of your add-in file:

Private Sub Workbook_Open()
    Set cbr = Application.CommandBars("Worksheet Menu Bar")
    Set ctl = cbr.Controls.Add(Type:=msoControlButton, temporary:=True)
    With ctl
        .Caption = "Your caption here"
        .OnAction = "'" & Thisworkbook.name & "'!macro_name_here"
        .Style = msoButtonCaption
    End With
End Sub