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]

How does the toolbar get created or deleted?
Is this a permanent toolbar that you created from the toolbar dialog boxes, or are you creating it in code?

Ditto for the macro assignment - does that happen in code when the workbook is opened?


I'm not sure I have any ideas - but we can try.

 

[9tanstaafl9]

Permanent. I wasn't even aware you could do it in the code. I used the standard attach method to attach it to my spreadsheet.

Macro assignment, is part of the workbook, not sure what you mean by that, sorry. This macro is only in this workbook.

 

[xenou]

So What happens to the button when the workbook isn't open? I assume it will (or should) say "Macro not found." Or do you hide it except when using this workbook? Is there a lot of other stuff on this custom toolbar?

It's probably not too hard to add a button with code. Might be more stable - as stable as any toolbar can be, given the fact that they've been phased out of Excel 2007 and 2010. In theory, we could even just run a short bit of code to find the button and check what's assigned to it (I *think*). I've been using right click menus for the last two years so I don't have to rewrite my toolbars when I switch to a new version of Excel

 

[9tanstaafl9]

I've been using right click menus for the last two years so I don't have to rewrite my toolbars when I switch to a new version of Excel

Hmm... I write in Excel 2003 so my stuff will work for people who have older systems, but these people happen to use 2007. At least most of them do. They weren't smart enough to remember the three ctrl keys for the macros they needed to run, which was why I made the menu. I didn't know they had been phased out of 2007 (shows how smart I am).

I'm going to abandon the menu altogether and try to figure out how to setup the macros to run from a right-click. Is that doable? Will it work for users of 2003 and 2007?

Thanks for the idea!

 

[xenou]

Yep - right click macros are not so hard as they sound. I'll find some links for you and see if I can post an example too. I've checked on my 2007 machine and all my right-click menus work great from 2003 with no changes.

 

[ZVI]

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

 

[9tanstaafl9]

Yep - right click macros are not so hard as they sound. I'll find some links for you and see if I can post an example too. I've checked on my 2007 machine and all my right-click menus work great from 2003 with no changes.

Sorry to bother you again, but could you post those links or the example? Now that I know toolbars don't exist in 2007, even though it looks like Vladmir solved my problem (I'm going to test that now), it looks like I'm going to need the right-click menus. I've been searching on the web, but the only sight I found was on Ozgrid, and it showed how to do ONE macro, and I need to add several. (This is a hard subject to search for, since right-click and toolbar are rather generic terms...)

Thanks!

 

[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

Thank you so much for explaining this to me. I must admit I thought I was going insane. I kept emptying all the temporary files I could find and restarting my computer. I couldn't figure out where the link was coming from that was causing this to happen. I deleted the toolbar and recreated it and nothing worked. Thank you so much!

Unfortunately I now know I have a bigger problem, since I use Excel 2003 (my husband's customers have really old computers), I hadn't realized that toolbars had been phased out of newer versions of Excel and my question was moot. Now I have to figure out how to add my macros to a right-click menu instead! Any links to this area would be appreciated.

Thank you anyway. I'm going to use your help for my personal use.

 

[ZVI]

It's ok! Additional knowledge never be redundant
Here is the simple example how to set right click menu:

' Put all code into standard module
Sub Auto_Open()
  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

Best wishes,
Vladimir