Custom Toolbar Macro Ghost?

[9tanstaafl9]

I keep reassigning a button on a custom toolbar to a link to a marco within my current workbook, and it magically transforms back to a link to a nonexistent workbook after I save and reopen the workbook. So when I press the button I get this message: 'Test.xls' could not be found. Check the spelling of the file name...

I very carefully reassign the macro, save my changes, save the workbook and close all the way out of Excel. I even tried closing all Exel documents and deleting the toolbar, so it didn't exist anywhere except in my newly saved workbook. I did this 10 times to be sure I didn't goof it up. And I deleted all references to the macro out of my PERSONAL.xls just in case that had anything to do with it.

Is there some sort of cache I need to clear or PRAM I need to zap like on a mac?

Arghhh!!!!

 

[xenou]

A few links (I wish I could summarize all my travels with command bars - I've learned it all by stumbling around):

I'm quite sure I've read this document several times (not start to finish, of course):
http://support.microsoft.com/kb/830502

From David McRitchie I discovered a great tip on using tags to identify controls - also a convenient syntax:
http://www.mvps.org/dmcritchie/excel/rightclick.htm

Which is also covered very well by Chip Pearson:
http://www.cpearson.com/excel/menus.htm

-----------------------------------------------------------------------

Here's a "working example" from a workbook on my pc.



Key points:

• I add the menu items to the right click menu when the workbook opens, and remove them when it closes (BTW: you can do the same with your "Personal.xls/b" and have your own personal right click options always present).
• There's actually more than one "Cell" command bar, so don't be surprised when you don't see these in print preview mode! That can be addressed if its a problem.
• From McRitchie and Pearson I've picked up the trick of using a common tag value or ID to make it easy to remove the controls - just beware that this will remove all controls with the tag so you should try to have different tag values for different workbooks you are adding right click items to.
• Consult the above first link if you are interested in menu items with sub-menus, buttons, and so on (I use text most of the time, without button images).
• Also note that its easy to add a command to run a procedure from your personal.xls workbook and so on - follow the samples given below and at McRitchie's site.
• Just to recap the important point - have an add menu items and a remove menu items subroutine. Call the add procedure from the workbook Open event, and call the remove procedure from the workbook BeforeClose event. As a precaution, also call the remove procedure in the add procedure, just before adding the controls. This will ensure that if the controls were left in place from earlier (say, if Excel crashed), you won't have them all twice.
• You can always reset the right click menu if you get into trouble (I think we see this on McRitchie's page as an example).
• Good luck! I'd like to just say thanks to Joe Was for posting a thread several years ago that got me started on custom menu items. Also, not to end pessimistically, but after 2003 its a brave new world of RibbonX. However, right clicks are working just fine in XL2003 and XL2007 with no changes.
• Sample code follows.

Sample Code - Workbook Module

Private [COLOR=navy]Sub[/COLOR] Workbook_Open()
    [COLOR=navy]Call[/COLOR] My_RMenus
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call My_Remove_My_RMenus
End Sub

Sample Code - Standard Module

[COLOR=navy]Option Explicit[/COLOR]
 
[COLOR=seagreen]'----------------------------------------------------------------------------[/COLOR]
[COLOR=seagreen]'//This constant is used to tag custom controls[/COLOR]
[COLOR=seagreen]'//A common tag will enable easy removal of all controls with the tag[/COLOR]
[COLOR=navy]Private[/COLOR] [COLOR=navy]Const[/COLOR] MY_TAG [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR] = "EOD_Processes_Workbook" [COLOR=seagreen]'//Should be unique[/COLOR]
[COLOR=seagreen]'----------------------------------------------------------------------------[/COLOR]
 
 
[COLOR=navy]Sub[/COLOR] My_RMenus()
[COLOR=seagreen]'RMenu = "[R]ight click menu"[/COLOR]
 
[COLOR=seagreen]'//Delete existing Right menus as a precaution (improper shutdowns/crashes could result in dupe menu items).[/COLOR]
[COLOR=navy]Call[/COLOR] My_Remove_My_RMenus
 
[COLOR=seagreen]'//Cell command bar is the right click cells menu.[/COLOR]
[COLOR=navy]With[/COLOR] Application.CommandBars("Cell").Controls
 
    [COLOR=seagreen]'//Add new menu item[/COLOR]
    [COLOR=navy]With[/COLOR] .Add
        .Caption = "Reformat EOD Report" [COLOR=seagreen]'//user sees this text[/COLOR]
        .OnAction = ThisWorkbook.Name & "!Run_EOD_Reformat" [COLOR=seagreen]'//name of a subroutine to run (see fourth menu item for why this syntax is useful)[/COLOR]
        .Tag = MY_TAG [COLOR=seagreen]'//We defined this constant in the declarations[/COLOR]
        .BeginGroup = True [COLOR=seagreen]'//Begin group adds a separator bar[/COLOR]
    [COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
 
    [COLOR=seagreen]'//Add a second menu item[/COLOR]
    [COLOR=navy]With[/COLOR] .Add
        .Caption = "Subtotal Detail"
        .OnAction = ThisWorkbook.Name & "!Sub_Totals"
        .Tag = MY_TAG
        .BeginGroup = False
    [COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
 
    [COLOR=seagreen]'//Add a third menu item[/COLOR]
    [COLOR=navy]With[/COLOR] .Add
        .Caption = "Remove Subtotals"
        .OnAction = ThisWorkbook.Name & "!Remove_SubTotals"
        .Tag = MY_TAG
        .BeginGroup = False
    [COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
 
    [COLOR=seagreen]'//Add a fourth menu item[/COLOR]
    [COLOR=navy]With[/COLOR] .Add
        .Caption = "Copy to New WB and Break Links"
        [COLOR=seagreen]'//Run a macro from my PERSONAL.xls![/COLOR]
        .OnAction = "PERSONAL.xls!SaveAndBreakLinks"
        .Tag = MY_TAG
        .BeginGroup = True
    [COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
 
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
 
[COLOR=navy]Sub[/COLOR] My_Remove_My_RMenus()
[COLOR=navy]Dim[/COLOR] y [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] cntrl [COLOR=navy]As[/COLOR] CommandBarControl
 
    [COLOR=seagreen]'//Delete tagged controls (custom and inbuilt controls tagged above)[/COLOR]
    [COLOR=navy]Set[/COLOR] cntrl = [COLOR=navy]Nothing[/COLOR]
    [COLOR=navy]Set[/COLOR] cntrl = Application.CommandBars.FindControl(Tag:=MY_TAG)
    [COLOR=navy]Do[/COLOR] Until cntrl [COLOR=navy]Is[/COLOR] [COLOR=navy]Nothing[/COLOR]
        cntrl.Delete
        [COLOR=navy]Set[/COLOR] cntrl = Application.CommandBars.FindControl(Tag:=MY_TAG)
    [COLOR=navy]Loop[/COLOR]
 
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Resulting Menu Changes

 

[ZVI]

Just a comment to my simple version of the code - if controls are added via .Add(Temporary:=True) and right click menu is intended to use till closing of Excel then deleting of those controls is not required.
For example, storing of right click macros in PERSONAL workbook is such case.

Temporary:=True means that "command bars are deleted when the container application (Excel) is closed". Controls added with Temporary:=True are not saved in XLB configuration file at all. Therefore in the next Excel session your controls don't appear automatically without running of the macro which creates your right click menu. So, all is under your control in this case.

Please take into account that Reset method (refer to my code) resets right click menu to the initial state.

BTW, there are different right click menus in Excel. For example under selection of the row(s) or column(s) the Application.CommandBars("Row") or Application.CommandBars("Column") would be used accordingly.
See info in the links kindly suggested by Xenou.

 

[ZVI]

I hadn't realized that toolbars had been phased out of newer versions of Excel and my question was moot

In new versions of Excel you can find your tollbars in the Ribbon's Developer tab which is displayed if you turn it on.

 

[xenou]

Yes, good point - the toolbars are still accessible on the developer tab (whether this is aesthetically appealing or does justice to the hard work you put into your custom menus I couldn't say - but at least the user can get to them! Which is some comfort). I imagine soon we'll all start getting better at RibbonX too

 

[9tanstaafl9]

In new versions of Excel you can find your tollbars in the Ribbon's Developer tab which is displayed if you turn it on.

Thanks, this is good to know!

 

[9tanstaafl9]

Just a comment to my simple version of the code - if controls are added via .Add(Temporary:=True) and right click menu is intended to use till closing of Excel then deleting of those controls is not required.
For example, storing of right click macros in PERSONAL workbook is such case.

Temporary:=True means that "command bars are deleted when the container application (Excel) is closed". Controls added with Temporary:=True are not saved in XLB configuration file at all. Therefore in the next Excel session your controls don't appear automatically without running of the macro which creates your right click menu. So, all is under your control in this case.

Please take into account that Reset method (refer to my code) resets right click menu to the initial state.

BTW, there are different right click menus in Excel. For example under selection of the row(s) or column(s) the Application.CommandBars("Row") or Application.CommandBars("Column") would be used accordingly.
See info in the links kindly suggested by Xenou.

So if what I'm understanding is correct, your recommended method is not quite as user friendly as Xenau's, because the commands would still be there when the original workbook is closed, but Excel is still open -- however, it is safer and less likely to cause problems with the user's system (in the event of a crash before the macro finishes or mistakes in my programming)? And considering that I am not onsite, and I want to cause absolutely zero risk of goofing up their right-click menus, this would be a safer way to go?

I am a relative newbie with VBA programming. I want to go the "better safe than sorry" route.

Regardless, I want to thank you so much for all the time and effort given to your answer.

 

[9tanstaafl9]

Thank you very much. I spent hours trying to find all this information and couldn't. I plan to spend a lot of time figuring this out in the near future. Much as I appreciate having you hand me the code already completed (and I do), I really like to understand how to do it myself. And the code you two gave me today/yesterday made zero sense compared to most of the stuff I see on the board, so I'm going to have to actually do some digging.

I'm starting to wonder how many years it's going to take before I start to feel even vaguely competent in all of this.

Thank you again for all your help!

 

[xenou]

So if what I'm understanding is correct, your recommended method is not quite as user friendly as Xenau's, because the commands would still be there when the original workbook is closed, but Excel is still open -- however, it is safer and less likely to cause problems with the user's system (in the event of a crash before the macro finishes or mistakes in my programming)? And considering that I am not onsite, and I want to cause absolutely zero risk of goofing up their right-click menus, this would be a safer way to go?

I think Zvi's approach is probably the best - the difference is that my requires explicit removal of the new controls, which only happens in the before close event or until the workbook is re-opened. So they might hang around after a crash, as an example.

Thank you very much. I spent hours trying to find all this information and couldn't. I plan to spend a lot of time figuring this out in the near future. Much as I appreciate having you hand me the code already completed (and I do), I really like to understand how to do it myself. And the code you two gave me today/yesterday made zero sense compared to most of the stuff I see on the board, so I'm going to have to actually do some digging.

I'm starting to wonder how many years it's going to take before I start to feel even vaguely competent in all of this.

No worries. Toolbars seem harder than they look - I avoided them for a while by using shortcut keys to run my own macros. Finally I dove in - its one of those things that will impress the heck out of your co-workers, too. A pity you'll have to re-learn it all for RibbonX, though.

 

[9tanstaafl9]

Hi,

If workbook with manually embedded custom toolbar (command bar) is loaded into Excel for the first time, then its toolbar is copied out from workbook to Excel and become the instance of Excel Application even after workbook is closed. Full pathname to the workbook and its macro is stored in OnAction property of each command button of toolbar.

Next time loading of that workbook doesn’t cause Excel to copy/refresh custom toolbar, as it is already present in Excel instance with toolbar controls’ properties previously saved in XLB file at first installation described above.

Issue comes when mentioned workbook is loaded from another folder or the name of workbook has been changed, or workbook with initial full path was moved/deleted/renamed.

To fix issue add the code below into ThisWorkbook module:

' Copy code into ThisWorkbook module of workbook with Custom Toolbar
Private Sub Workbook_Open()
Const MyToolbarName = "9tanstaafl9" ' <-- Change to suit
On Error Resume Next
With Application.CommandBars(MyToolbarName)
  .Visible = True
  ' In the lines below replace "MyMacro1", "MyMacro2" by the names of your macros
  .Controls(1).OnAction = "MyMacro1"  '<-- Without path!
  .Controls(2).OnAction = "MyMacro2"  '<-- Without path!
  ' ... and so on
End With
End Sub

After loading of workbook the path will be removed from OnAction properties of Custom Toolbar and the macros of the current workbook will play.

Another way is in removing of your Toolbar from Excel Application by triggering of BeforeClose event.
Position of toolbar will be removed in this case which is not user frendly.
But storing of the position in the regestry will be implemented.

Regards,
Vladimir

Vladmir,

Thank you so much for your help with this. I finally got my workbook back and got a chance to implement the code you were so kind as to give me, and it worked perfectly. I especially appreciate it that you took the time to explain what it meant so that I wasn't just stealing your work. Now I understand -- at least a little -- and was able to modify it for the 4 other macros they had me add. Thank you again! I will use this often. Thank you to Xenou too!

Jennifer

oops, I just realized I posted this into the wrong one your posts, the code I actually used was this:

' Put all code into standard module
Sub Autpen()
With Application.CommandBars("Cell").Controls
.Parent.Reset
With .Add(Temporary:=True)
.BeginGroup = True
.Caption = "My Macro1"
.OnAction = "'" & ThisWorkbook.Name & "'!Macro1"
End With
With .Add(Temporary:=True)
.BeginGroup = True
.Caption = "My Macro2"
.OnAction = "'" & ThisWorkbook.Name & "'!Macro2"
End With
End With
End Sub

Sub Macro1()
MsgBox "Hi from Macro1"
End Sub

Sub Macro2()
MsgBox "It's Macro2"
End Sub

 

[ZVI]

Glad you've got it working!
Just remember that Xenou’s suggestion deletes the controls more precisely.
Application.CommandBars("Cell").Reset method used in mine suggestion deletes all non built-in controls even those added via add-ins or via other code.
Cheers