Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    custom toolbar (excel 97) (97)

    I have a big spreadsheet with several userforms. I used to show the userforms with control buttons in the spreadsheet. I thought that it would be better to have, instead, a custom toolbar to do the same thing, as it could be attached anywhere outside the spreadsheet. No problem with that. I built the toolbar and assigned each command a macro that performs userform.show.

    the problem is that I generally update the spreadsheet and save it with another name. When I open the file with the new name, the macros in the toolbar are still referenced to the file with the old name.

    How can I avoid that?. I want the toolbar to be available only with my custom spreadsheet and not show up when I open other files. And if I share this file in a network, I want the toolbar to show up In any PC when the file is opened.

    Thank you
    Guillermo

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

    Re: custom toolbar (excel 97) (97)

    Attach the toolbar to the workbook (Tools/Customize..., Attach button).
    Then write code to destroy the tool bar when you close the workbook.

    In the VBE editor, double click This Workbook and add code like the following:

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

    This way, the custom toolbar is only active when the workbook it is stored in, is opened.

    A drawback is, that if the user cancels the closing of the workbook (when being asked whether he/she wants to save it), the toolbar has already been removed. An alternative is to use the Activate/Deactivate events. If you do a search on the Excel and VB/VBA threads, you will find a lot more on this subject.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: custom toolbar (excel 97) (97)

    As Hans pointed out, the best way is to create the toolbar on the fly, and then destroy it when leaving the book. You can also use the following to trap the cancel and avoid deleting the toolbar until the user actually decides to close the book:

    If Cancel = False Then Exit Sub

    If you search the Lounge, you will get many examples of code for creating toolbars on the fly.

Posting Permissions

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