Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Atlanta, Georgia, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sharing Excel macros (2000)

    I am very familiar with the options for distributing macros in Word, but I don't know best practices for doing the same in Excel.

    I have some macros that I use (all stored in PERSONAL.XLS). Is it as simple as moving the code to an XLT file and then sending that to my co-workers? How do they activate the template?

    What about toolbar buttons? I don't see any way to "associate" a toolbar with a particular template.

    Can anyone give me some pointers?

    Thanks,
    Bob
    Writing Consultant / Word Template Designer
    Words Connect

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sharing Excel macros (2000)

    One of your options is to save the code in an Excel Add-in (one of the Save As options). You would, however, have to create a custom toolbar, as code in Add-ins is not available through the Macro|Run command. The code would be .OnAction event triggered by the CommandBarBattons on the custom toolbar.

    Could you be a bit more specific as to what you are driving at in terms of toolbar buttons?
    Gre

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Atlanta, Georgia, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sharing Excel macros (2000)

    I didn't know about saving the project as an Add-In. That seems to be what I want to do. However, I can't figure out how to assign macros on a toolbar to an add-in.

    I moved the code over from my Personal.xls to a new .xlt file. I then created a toolbar and assigned the buttons to the procedures in the .xlt file. Then I saved the file as an add-in, but the buttons still reference the .xlt file. I can't seem to open the .xla file to modify it. What am I misunderstanding?

    Thanks!
    Bob
    Writing Consultant / Word Template Designer
    Words Connect

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

    Re: Sharing Excel macros (2000)

    Make sure the add-in is loaded (Tools | Add-ins...)
    Activate the Visual Basic Editor (Alt+F11)
    Select ThisWorkbook in your xla in the Project Explorer.
    Set the IsAddin property to False (this is temporary)
    Switch to Excel (Alt+F11)
    Select Tools | Customize...
    Assign your macros (in the xla) to the toolbar buttons.
    Attach the toolbar to the xla (Attach button in the Toolbars tab)
    Switch back to the Visual Basic Editor.
    Double click ThisWorkbook in the Priject Explorer.
    Create a BeforeClose event procedure:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.CommandBars("MyToolbar").Delete
    End Sub

    Replace MyToolbar with the appropriate name.
    Set the IsAddin property to True again.
    Save your add-in now.

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sharing Excel macros (2000)

    You're not misunderstanding much at all. The following should move you further down the road.

    The thing about Excel Add-ins is that once they are saved as an Add-in, you can access the code through the VBE but not the front end. A common way to deal with the toolbar is to create and delete it on opening and closing the file. Something like: <pre>Sub AddReviewBar()
    Dim cBar As CommandBar
    If ToolbarExist("review") Then 'delete original toolbar if necessary
    CommandBars("review").Delete
    End If
    Set cBar = CommandBars.Add("review", msoBarTop) 'This puts the Toolbar on top
    With cBar
    .Controls.Add(Type:=msoControlButton).Caption = "&Formulas" 'this is whatever _
    'caption you wish
    .Controls(1).OnAction = "Formula" 'this is the name of the procedure you wish _
    'to run with the code
    .Controls(1).FaceId = 477 'FaceIds are as used in Word
    .Visible = True
    End With
    End Sub</pre>

    To make the file appear when the Add-in is loaded use - and close when it is unloaded you can use:<pre>Sub Auto_Open()
    AddReviewBar
    End Sub

    Sub Auto_Close()
    On Error Resume Next
    CommandBars("review").Delete
    End Sub</pre>

    You can also use the Workbook_Open and Workbook_BeforeClose events - with the same code - respectively. These events are not held in a general Module but in the ThisWorkbook object - locatable in the Project Explorer (at the top left of the VBE).

    For the most part, people tend to use Templates in Excel to store formatting choices - although they can be used for storing code. Once a file has been saved as an Add-in, it ceases to be recognised by Excel as a Template.
    Gre

  6. #6
    2 Star Lounger
    Join Date
    May 2002
    Location
    Atlanta, Georgia, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sharing Excel macros (2000)

    Thanks Hans for your help (and thanks to Unkamunka too). I have been able to get things working much better, but I still have a couple of questions:

    * Why should I use the Before_Close event? I am attaching my toolbar to the add-in instead of building it programmatically each time. The first time I tried the Before_Close, it deleted my toolbar from the add-in and I had to redo it. Is the Before_Close event only to clean up the toolbar if you're creating it on the fly?

    * My add-in is loading automatically, but I still get the macro warning the first time I click on a button to run a macro. The .xla is stored in the Add-Ins directory. Is there something else I should do?

    Thanks!
    Bob
    Writing Consultant / Word Template Designer
    Words Connect

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sharing Excel macros (2000)

    Here is my boilerplate answer about toolbars:

    Excel keeps toolbar and menubar customizations in a file with the extension .xlb. The exact filename depends on Excel version and install, but usually is: Excel9.xlb or Excel.xlb or Username8.xlb.
    Often this file can be found in your WINDOWS directory.

    You can attach a toolbar to a workbook. When this workbook is loaded, XL checks if the toolbar is on the system. If not, it copies the toolbar from the workbook to the system.

    After creating *or changing* the toolbar, you should attach the toolbar to your workbook:

    - activate the workbook to which you want to attach the toolbar
    - Rightclick the toolbar, select 'customize'
    - Click 'Attach' (Toolbars Tab)
    - If the workbook already contains a toolbar by that name, delete it first by clicking on it on the righthand side and choosing Delete.
    - Select your toolbar (on the left) and press 'copy'
    - Save the workbook (optionally: save_as an add-in).

    Also, you should include code that deletes the toolbar when your workbook or add-in is closed, so that when you deliver a new version of your workbook the new toolbar will be used i.s.o the old one. You can do that in the Thisworkbook module, using the Workbook_BeforeClose event:

    Private Sub Workbook_BeforeClose(Cancel as Boolean)
    On Error Resume Next 'In case Toolbar is absent
    Application.CommandBars("YourBarsName").Delete
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sharing Excel macros (2000)

    On the Warning front, you should digitally sign your macro.

    If you don't want to buy a third party solution, there is a program called SelfCert.exe in you Program FilesMicrosoft OfficeOffice directory. Once you set up the certificate, it should appear in your certificates directory - under Control Panel|Internet Options. (If you use the MS method, this should happen automatically.) The code is then signed inside the VBE, using Tools|Options.

    Depending on the version of MS Office, the end user will have to go through steps to Trust the certificate. In Office 2K, the option is presented when the signed code is first run. In later versions, the user must, I believe, press the More Info button and then work their way through the certificate details to import it into their Trusted Store. After having done that, the code will run without problems. <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

    <!profile=pieterse>pieterse<!/profile>'s response on deleting the toolbar is fairly comprehensive. Basically, it is good practice to delete the toolbar. Among other things, this reduces the possibilities of its becoming corrupt over time and thus interfering with the running of your code.
    Gre

  9. #9
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sharing Excel macros (2000)

    I'm using code similar to this in the budget template which I will be distributing to users throughout the company. I've run into a problem with deleting the toolbar when the workbook is closed, however. If the user is working in one workbook and then opens another based on the same template--for reference or just to be working on two at once (which will happen often, I'm sure)--when he then closes the second workbook, he loses the toolbar for the first. Is there any way to track how many workbooks are open which include the specified toolbar and only delete the commandbar if no others are open? Or something like that? Many thanks!

    --Karyl

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sharing Excel macros (2000)

    That is possible, you would need to scan all open workbooks (For Each oBook In Workbooks) and check them for something that tells you tehy "belong" to that template. Maybe a document property?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sharing Excel macros (2000)

    That's how I would have done it in Word, but Excel's template property didn't seem to have a value. I tried using a custom document property, but if an open workbook didn't have that property, I got an error and couldn't figure out how to trap it. Eventually, I tried setting the "Template" BuiltinDocumentProperty directly through code, and--lo, and behold--it worked! So this is what I ended up with:

    <pre>Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim wb As Workbook
    Dim i As Integer

    'If the user cancels the close, DON'T delete the toolbar
    If Cancel = True Then Exit Sub
    i = 0
    'Check all the open workbooks and see if any of the others were based on this template
    For Each wb In Application.Workbooks
    If wb.BuiltinDocumentProperties("Template").Value = "PMP Budget.XLT" Then
    i = i + 1
    End If
    Next wb

    'And if this is the only open workbook using this toolbar, then delete it.
    If i = 1 Then
    On Error Resume Next
    Application.CommandBars("Budget Toolbar").Delete
    End If

    End Sub</pre>



    Setting the property was a one-time deal in the template itself. Subsequent documents based on the template carried over the value. Many thanks!

    --Karyl

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sharing Excel macros (2000)

    That's one of the main reasons that you should use the wrokbook Activate/Deactivate events to build and delete your toolbars. If you do it in the Open/Close events you have this and other problems.
    Legare Coleman

  13. #13
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sharing Excel macros (2000)

    I looked into that method, but the toolbar as it exists right now is a constantly evolving entity. There are already 35 or 40 menu items with many more to come (which menus display depends on the sheet selection, so they aren't all visible all the time). Many of the items have custom images which I didn't know how to access since they don't have an ID. I have thought that--maybe--after the workbook is done and all the menus are finalized, I'll try creating it on the fly each time. For now, I'm living with it this way.

    --Karyl

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sharing Excel macros (2000)

    What are you doing in the close event routine and where are you doing the opposite. Whatever it is should be able to be done in the workbook activate/deactivate event routines. If you have sheet specific menus, those should be displayed/hidden (or built/delete) in the sheet activate/deactivate event routines. If you are doing differently, you are going to have to do a lot of extra coding, or live with problems like you describe.
    Legare Coleman

  15. #15
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sharing Excel macros (2000)

    I'm showing and hiding the sheet specific menus via the Workbook_SheetActivate event. I named the menu controls with the same names as the sheets, so the code works something like this:

    strSelected = Sh.Name
    Application.CommandBars("Budget Toolbar").Controls(strSelected).Visible = True
    Application.CommandBars("Budget Toolbar").Controls(strPrevious).Visible = False
    strPrevious = strSelected

    The problem with building the toolbars in code is that right now they are still largely conceptual. Some items are there, some are still to come, some will be deleted. It is a work in progress. And I'm designing a lot of the buttons myself, which I need to do via Excel's Customize commands vs. VBA code. I don't have a clue if it is even possible to design a custom button image via code or to save one somewhere and retrieve it later. This probably isn't the best way, but it may be the only way on this project. We decided late yesterday to wait to launch it until everyone has moved to Office 2003, so I may be doing parts of it over, anyway. We'll see what .NET options have to offer. I haven't even started playing with the new version!

    --Karyl

Page 1 of 2 12 LastLast

Posting Permissions

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