Custom Toolbar Macro Ghost?

9tanstaafl9

9tanstaafl9

Well-known Member
Joined
Mar 23, 2008
Messages
535
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!!!!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
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.
 
Upvote 0
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.
 
Upvote 0
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 ;)
 
Upvote 0
xenou said:
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 ;)
Click to expand...

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!
 
Upvote 0
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.
 
Upvote 0
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:
Rich (BB code): 

' 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
 
Last edited:
Upvote 0
xenou said:
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.
Click to expand...


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!
 
Upvote 0
ZVI said:
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:
Rich (BB code): 
' 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
Click to expand...

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. :cool:
 
Upvote 0
It's ok! Additional knowledge never be redundant ;)
Here is the simple example how to set right click menu:
Rich (BB code): 

' 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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

pcc
  • Solved
Upgrade from Excel 2002 to Excel 2021 - where is my customisation?
Replies
1
Views
182
pcc
pcc
M
  • Solved
How to move custom VBA/ribbons
2
Replies
12
Views
606
missingsc
M
J
  • Question Question
VBA for Excel used to work, doesn't now
Replies
1
Views
228
Joe4
Joe4
A
  • Solved
Copy Entire Workbook, save as sheet name with current date, hide all sheets except one, & attach to email
Replies
2
Views
614
Amo840812
A
T
  • Question Question
Custom ribbon Delete button status at runtime
Replies
6
Views
294
tiredofit
T

Forum statistics

Threads
1,224,599
Messages
6,179,831
Members
452,946
Latest member
JoseDavid

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top