Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Customizing Toolbars (2000/SP3)

    I have a unique toolbar and associated macros saved in a workbook. one of the macros being Auto_Close whose sole purpose is to delete the unique toolbar from the Personal workbook. I have no problems with this aspect of the situation; all works well with the toolbar appearing and disappearing on-cue.
    On modifying the overall approach to my project, I elected to remove one of the menu items from my unique toolbar. Here's the rub; Using the Customize feature, I drag the menu item off the toolbar, then close the Customize panel. All seems well. Then I:

    1. <LI>Right click in the Toolbar area.
      <LI>Select Customize
      <LI>Select the Toolbar tab
      <LI>Click Attach
      <LI>Select the unique toolbar in the right-hand window
      <LI>Click Delete
      <LI>Select the unique toolbar in the left-hand window
      <LI>Click Copy
      <LI>Click OK
      <LI>Close the Customize window
      <LI>Save and close the workbook
    I find that frequently after following this process; on re-opening the file, the removed menu item has re-appeared. I am now about to enter into a tedious program of recording exactly what steps I have taken and the detailed results at each step in an attempt to determine what is happening. Can someone contribute any experience to reduce this effort?
    As always!
    Regards
    Don

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

    Re: Customizing Toolbars (2000/SP3)

    >delete the unique toolbar from the Personal workbook

    I'm not altogether clear what you are attempting. My personal inclincation would be to have code rebulding the Toolbar in the Auto_Open sub of the code module in the Target Workbook. This would match the Auto_Close code.

    HTH
    Gre

  3. #3
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Quakertown, PA, Pennsylvania, USA
    Posts
    517
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Customizing Toolbars (2000/SP3)

    Custom toolbars are much nicer when they're entirely built and deleted using code.
    Also, auto_open is kinda defunct. MS says use workbook_open instead, tho it's been a long time since I've been able to find that reference again.
    Here's a bit of a debate about it. http://www.dicks-blog.com/archives/2004/08...book-is-opened/

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Customizing Toolbars (2000/SP3)

    Unkamunka
    Thanks for the guidance. I have often considered the build-with-VBA approach, but have been unable to find a suitable example to learn from. I need to build a toolbar that looks like:

    Macros (This item opens the following pull-down list)
    Macro1 (This item runs a specific macro)
    Macro2 (This item runs a specific macro)
    Macro3 (This item starts a new group and runs a specific macro)

    If you or any other Lounge members could point me to some sample code which builds a command bar with these elements, I would be most grateful.
    Regards
    Don

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Customizing Toolbars (2000/SP3)

    Dreamboat
    Thank you for the insight into Workbook_Open. I have just recently delved into the VBA 6 Event Handler, and do agree that the Workbook_Open is the way to go for any new code I develop. Unfortunately in the past I have been making use of Auto_Open's characteristic of not running when the project is opened by code, which makes it risky for me to change existing code.
    Regards
    Don

  6. #6
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Quakertown, PA, Pennsylvania, USA
    Posts
    517
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Customizing Toolbars (2000/SP3)

    Here's a Word one: http://www.vbaexpress.com/kb/getarticle.php?kb_id=14

    Excel is virtually the same.
    That one creates a menu to the right of the Help menu, and drops down (like a real menu! hee hee) and shows the macros it runs.
    You'll run it on Workbook_Open of course.
    Let me know if you need help with making it work in Excel. While I did not create that KB entry, it WAS created for a project of mine.

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Customizing Toolbars (2000/SP3)

    Hi Don

    Unkamunka has probably forgotten this <img src=/S/grin.gif border=0 alt=grin width=15 height=15> but he provided me with just such an example in <post#=121736>post 121736</post#>. The whole thread, including <post#=122246>post 122246</post#> is probably worth reading. I'd say there's enough there to start off with.

    Alan

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

    Re: Customizing Toolbars (2000/SP3)

    There are two reasons I suggested that you consider Auto_Open. One is that you were already using Auto_Close - thus less disruption to your existing method. The other is that, inevitably, over time workbooks become corrupted. If you back up the entire standard Module, it can be easier to recover from a situation.

    A tip of the hat to <!profile=AlanMiller>AlanMiller<!/profile> for digging up my old post. You may wish to consider something like:<pre>Private Sub AddBar()
    StatusBar = "Setting up Custom Toolbar"
    Dim cBar As CommandBar
    Set cBar = CommandBars.Add("Custom", msoBarTop) 'Top puts the Toolbar above the workbook.
    cBar.Controls.Add(msoControlDropdown).Caption = "&Macros" 'The & enables a hotkey
    Set subControl = cBar.Controls(1)
    With subControl
    .Controls.Add(msoControlButton).Caption = "Macro&1"
    .Controls.Add(msoControlButton).Caption = "Macro&2"
    .Controls.Add(msoControlButton).Caption = "Macro&3"
    'The next line puts a group divider between the second and third buttons
    .Controls(3).BeginGroup = True
    End With
    End Sub</pre>

    HTH
    Gre

  9. #9
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Customizing Toolbars (2000/SP3)

    Actually I highly recommend using BOTH auto_open and workbook_open as I've had cases where a previous workbook has left events disabled. You seem to have been bitten by this as well since you mention a similar situation. What I do is have code which 'kick starts' the events inside of auto_open which then calls the required workbook_open code. This works since auto_open will run w/events disabled, but workbook_open won't.

    <pre>' in Thisworkbook
    public sub auto_open
    If DidWBOpen Then Exit Sub
    Application.EnableEvents = True
    Call WBOpen
    end sub

    Private Sub Workbook_Open()
    Call WBOpen
    End Sub</pre>

    <pre>' in a separate module:
    Public DidWBOpen As Boolean

    public sub WBOpen()
    DidWBOpen = True;
    ' code here to run at workbook_open
    end sub</pre>

    Deb

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

    Re: Customizing Toolbars (2000/SP3)

    The most likely cause for the button to reappear is that the "old" toolbar is still on your system. Delete the old toolbar and close and reopen the file should fix that.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Customizing Toolbars (2000/SP3)

    [tab}Thank you so much. I misguided you with my description of my needs, I required a popup as opposed to a dropdown list; but that minor discrepancy has been solved. I now have the code running well but I am uncertain of the correctness of the following line I inserted to allow the code to run in an "Option Explicit" module.
    <pre> Dim subControl As Variant</pre>


    Your comments will be appreciated.
    Regards
    Don

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

    Re: Customizing Toolbars (2000/SP3)

    It'll work, but you won't get IntelliSense. To profit from IntelliSense and syntax checking, declare it as

    Dim subControl As CommandBarPopup

  13. #13
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Customizing Toolbars (2000/SP3)

    Thanks for the help Dreamboat
    I will be using the very simple code provided by Unkamunka in this instance, but am holding onto your code offering for my Word needs.
    By the way; I think you deserve some sort of award. Perhaps "<font color=red>Speedy Response of the Year</font color=red>" would be appropriate.
    Regards
    Don

  14. #14
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Customizing Toolbars (2000/SP3)

    Of course!
    Thank you Hans.
    Regards
    Don

  15. #15
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Customizing Toolbars (2000/SP3)

    Thanks Deb
    Regards
    Don

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
  •