Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Distributing an Excel application with toolbars (5/95/97/2000/2002)

    Hi all,

    Thought I'd write this down for all of you who want to distribute some neat macro's and toolbars you have created to colleagues and friends.

    Many users tend to put their macros in their personal.xls file. Nothing wrong with that, because that is what it is there for. But what if you like your macros so much you decide others might benefit?

    *** EDITED JKP, 25 nov 2002, added this sentence:

    <font color=red>You might be tempted to copy your personal.xls onto a floppy and give that to others. Don't!</font color=red>
    Also don't copy your xlb file to others to give them your toolbars, you will overwrite their customisations..

    I would be very distressed if you would come and hijack my personal.xls and excel.xlb files!

    What you should do is create a new workbook with all your code *and toolbars* and distribute *that* file (maybe saved as add-in).

    About toolbars:

    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()
    On Error Resume Next 'In case Toolbar is absent
    Application.CommandBars("YourBarsName").Delete
    End Sub

    Hope this clarifies things about distributing macros a bit!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Distributing an Excel application with toolbars (5/95/97/2000/2002)

    I have a custom toolbar loading from my personal.xls called s-LYNX. I have this code in ThisWorkbook

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar").Controls("s-LYNX").Delete

    End Sub

    but it won't take the menu item off. In fact, everytime i open Excel it adds another menu. So after awhile I have 5 or 6 s-LYNX menus. What am i doing wrong?

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

    Re: Distributing an Excel application with toolbars (5/95/97/2000/2002)

    You're confusing me. First you say you have a custom toolbar, but after that it appears to be a menuitem. Which is it?

    I'm not sure, but I think code for manipulating commandbars cannot be in the thisworkbook module.

    When you add a menuitem, use it's temporary parameter, set it to true so it will be removed when Excel closes.

    Also, I dim a global variable called cControl at the top of my normal module:

    Public cControl as commandbarcontrol

    In the sub (in a normal module!) I use to add the control to the menu, I do this:

    Sub MakeMenu()
    On Error Resume Next
    Application.EnableCancelKey = xlDisabled
    RemoveMenu 'Prevent duplicate menu's
    Set cControl = Application.CommandBars(1).Controls(1).Controls.Ad d(, , , 4, True)
    cControl.Caption = "YourControlName
    cControl.OnAction = "changesettings"
    On Error GoTo 0
    End Sub

    Then use cControl to remove the item again.

    Sub RemoveMenu()
    On Error Resume Next
    cControl.Delete
    On Error GoTo 0
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Distributing an Excel application with toolbars (5/95/97/2000/2002)

    sorry, i meant a custom menu added to my Worksheet Menu Bar.

    ok, so i don't need to put the code in ThisWorkbook for the open or before_close event. I'll try your solution. Thanks for the help

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

    Re: Distributing an Excel application with toolbars (5/95/97/2000/2002)

    Of course you *do* need to call the two subs I provided from the Workbook_Open and BeforeClose routines!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distributing an Excel application with toolbar

    > Hope this clarifies things about distributing macros a bit!

    Your hopes have come true, and with that truth comes thanks.

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

    Re: Distributing an Excel application with toolbar

    You're welcome.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distributing an Excel application with toolbar

    It works, but I have residual confusion.

    I'm struggling to comprehend a system which apparently will "break" on a user who unwittingly goes through the mechanical process of recording a macro, assigns it to a user-created toolbar, saves the workbook, and then renames it in File Explorer.

    Your fix works, but it seems to be an extra level of complication for the average user.

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

    Re: Distributing an Excel application with toolbar

    The average Excel user is an end user who will have little need to distribute workbooks with toolbars to others. Macros for repeated use (as opposed to a one-off that is created, run and discarded) can be stored in the personal macro workbook Personal.xls. This name is fixed, so there should be no problems.

    See Legare Coleman's <!post=Personal.xls Tutorial (All),118382>Personal.xls Tutorial (All)<!/post> for details and instructions.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Distributing an Excel application with toolbar

    I am not sure I understand completely your question.

    I read your question to be:
    Someone records a macro,
    Assigns it to a toolbar button
    [At this point the button points to a macro in a particular file]
    Now you save the workbook
    Close excel, etc
    In windows explorer, you rename the file that has the macro in it.

    And you want to know why excel can't find the macro?

    If you worked at a company and had to file something. You created a memo, and and asked your secretary (or Admin Asst, or coworker or whatever) to file it in a particular place. If that person decided to put it elsewhere or move it later to a new location (without telling you where), would you be surprised when you looked and couldn't find it? You would not be able to find it until you were told its new location.

    It is the exact same principle.

    Steve

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distributing an Excel application with toolbar

    > I am not sure I understand completely your question.

    I think you did. You give a good example, and I immediately thought of MSWord:

    In MSWord, Someone creates a Style.
    Assigns it to a document.
    (At this point the style points to an set of definitions in a file)
    Now you save the document, close Word
    In Windows Explorer you rename the file that has the style in it

    And you email the file across the world. The recipient opens it and the style is there, can be applied, modified etc.

    So, it is a good example you created, on which I was able to model my Word example and asked myself "What's the difference?", for there is a difference.

    In both Excel and Word the end-user can create a unique definition (macro, style), and there is some sort of pointer (although I'll have to think about this), in both cases the user can rename the file, but in Word this has no impact while in Excel the impact can be enormous.

    In Excel the pointer is clearly visible to us end-users I can see the actual name of the file, and when I rename the file, I obtain an error message with the name of the original file ("Can't find Waldo" or similar). The macro and toolbar certainly seem to travel from Toronto to Guelph (which from Toronto's point of view the end-of-the-earth), and the end-user there can see the toolbar. But can't run the macro from the toolbar.

    It seems to me that the end-user's point of view is consistent "I created a custom thing in my file, saved then renamed it". In Word this works, in Excel it doesn't.

    My end user is reasonably sophisticated; teaches Excel & macros, Word & styles. Since I'm doing work for them I'm supposed to be more sophisticated than they. That I'm stumped by the toolbar problem indicates that I don't know as much about macros/toolbars as I thought I did. Explaining MS's behaviour to my client is a little harder.

    In conclusion, it's working now, but I claim that the Excel and Word models offer quite different philosophies.

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

    Re: Distributing an Excel application with toolbar

    > I claim that the Excel and Word models offer quite different philosophies.

    That is true. Even after living in the same Office for more than a decade, there are still fundamental differences in the way Word, Excel, PowerPoint, Outlook and Access handle macros and customizations.

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Distributing an Excel application with toolbar

    Part of these different philosphies is where the info is stored. Many of words "enhancements" are stored in normal.dot but others are stored in the document itself.

    Excel stores some things in the workbook and other things are available to all. Toolbars are stored in the users customization file (.xlb file) so are available to all workbooks so the buttons don't point to something like "thisworkbook" but are explicit to where they point.

    It is similar to macros which can refer to an explicit workbook, activeworkbook, or thisworkbook. The might all be the same or they may be different depending on the conditions when the code is run. Which you use in code depends on how you want the code to run and what you want it to refer to.

    Toolbars are always explicit. There is not the equivalent "activeworkbook" or "thisworkbook" in the code. It is hardcoded with the name of the file. It should work correctly and "repoint" if you open the file and then saveas a different name. I think the toolbars will then point to the new name not the original. You are changing the file name and letting excel know where it is and so it will find it in the future. [Like your coworker moving your file and telling you the new location]

    Hope this helps,
    Steve

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distributing an Excel application with toolbar

    > Part of these different philosphies is where the info is stored

    I think it is more a question of HOW it is stored. In both Word and Excel material is stored in the document, but Excel insists on hard-coding a pathname for macro items in a toolbar. That reduces the transportability, as in my example of an end-user renaming a workbook.

    As you point out, toolbars are explicit, in my opinion, horribly so. That explicit path behaviour is the opposite of Word's implementation.

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Distributing an Excel application with toolbar

    But, like I said, the toolbars are not stored with the workbook. It thus makes no sense to have anything but an explicit coding of the name.

    I didn't create the program, I only work with it and try to work within its bounds and limitations ("stretching" as many items and techniques as I can <img src=/S/grin.gif border=0 alt=grin width=15 height=15>).

    The best way to do menus for sharing with others is to create the menus when the workbook is opened/activated and delete them when the workbook is closed to ensure that they don't "stick around". This technique is discussed in this thread.

    Steve

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
  •