Custom Toolbar Issues

Benners

New Member
Joined
Jan 17, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
I have "inherited" a spreadsheet that was created in 1999. The sheet runs on an old version of office on the PC and is fully working, but move the sheets to another PC and errors occur. The sheet is for work and there is a custom toolbar on with some functions. There are two sheets, one is the main sheet and the other is a dummy in another workbook referenced from the main sheet, and this dummy workbook is the one with all the vba code. I dont know it's been done this way.

I have searched the code for the creation of the toolbar but I can only find code that shows the toolbar. This happens on opening the workbook. On clicking the toolbar buttons, an error dialog is shown (attached). It looks to be trying to save to a file? There seems to be a file name in the dialog. Using Ctrl + Shift + i on the dialog, gets the error message number in the bottom corner. I have tried googling it but nothing obvious is coming up.

I want to be able to edit the toolbar actions and buttons and also to back up the sheets and associated files incase the PC goes down (this is the only PC with it on). I have looked in Options - Addins and there are only the default ones when installing Office and again with no code that I can see that to create the toolbar, I am wondering where else to look and how the toolbar is being created.

Any ideas on what a next step could be?

Thanks
 

Attachments

  • Excel Toolbar.png
    Excel Toolbar.png
    35 KB · Views: 14
  • Excell_Error.png
    Excell_Error.png
    68.7 KB · Views: 14

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
It sounds like the workbook had a custom toolbar attached to it that calls macros in another workbook. It is not possible to view or edit such toolbars in current versions of Excel (other than deleting them), other than programmatically.
 
Upvote 0
I have added a test function that deletes the toolbar that works but when the code is run to show (below) it it obviously comes back
VBA Code:
With Application.CommandBars("Oven Bay Scheduler")
        .Visible = True
        .Position = msoBarTop
        .Protection = msoBarNoChangeVisible + msoBarNoMove + msoBarNoCustomize
    End With

Would queriying the toolbar be possible on the Office version that in on the same PC.
I am looking to remove the old toolbar and create a new one in VBA. I can guess some of the macros that the buttons would use but I don't know if they do any clean up saving etc that is not in the macros.
In the error dialog, there is a file name obs temp 1|d.xls which makes me think it is writing to a temp file first?

Thanks for the reply
 
Upvote 0
You can find out the macros it is using by checking the onaction property for the controls - for example:

Code:
for each ctl in Application.CommandBars("Oven Bay Scheduler").controls
        debug.print "'" & ctl.caption & "' runs the '" & ctl.onaction & "' macro"
next ctl
 
Upvote 0
Solution
Thanks Rory

I added on error resume next as it balked when it got to the zoom control. This is what return, I assume it goes in order of buttons on the tool bar. (is there a property to get the button text?)

'2950' runs the ''C:\obs111\obs temp 1ld.xls'!OnOpen' macro
'2950' runs the ''C:\obs111\obs temp 1ld.xls'!OnComment' macro
'4' runs the ''C:\obs111\obs temp 1ld.xls'!OnPrint' macro
'3' runs the ''C:\obs111\obs temp 1ld.xls'!OnSave' macro
'752' runs the '' macro
'2950' runs the ''C:\obs111\obs temp 1ld.xls'!OnUnlockProducts' macro
'2950' runs the ''C:\obs111\obs temp 1ld.xls'!OnLockProducts' macro
'2950' runs the ''C:\obs111\obs temp 1ld.xls'!OnCheckProducts' macro
'2950' runs the ''C:\obs111\obs temp 1ld.xls'!OnShowProduct' macro
'2950' runs the ''C:\obs111\obs temp 1ld.xls'!CreateStillage' macro
'2950' runs the ''C:\obs111\obs temp 1ld.xls'!UpdateStillage' macro
'2950' runs the ''C:\obs111\obs temp 1ld.xls'!DeleteStillage' macro
'2950' runs the ''C:\obs111\obs temp 1ld.xls'!OnShowStillageRecord' macro
'2950' runs the ''C:\obs111\obs temp 1ld.xls'!Schedule' macro
'2950' runs the ''C:\obs111\obs temp 1ld.xls'!IncreaseRowHeight' macro
'2950' runs the ''C:\obs111\obs temp 1ld.xls'!DecreaseRowHEIGHT' macro
'2950' runs the ''C:\obs111\obs temp 1ld.xls'!IncreaseColumnWidth' macro
'2950' runs the ''C:\obs111\obs temp 1ld.xls'!DecreaseColumnWidth' macro

The obs temp file name should be invalid as it contains a pipe symbol and I can't recreate the file on this PC. I have opened the xlsm file (renamed from an xls) and found the attachedToolbars.bin. Using a hex editor I could see the references to the temp file. I am going to try to edit the bin file and remove references to the "|" and see if it works.
 
Upvote 0
Sorry, to low a post count to edit the last post but I meant the tooltip, not the text as these buttons don't have any. Gonna google as well
 
Upvote 0
There is also a TooltipText property for the control.
 
Upvote 0
Cheers. I found that after a quick google. Didn't want to keep posting replies instead of editing. I'll mark this as sorted. Thanks again
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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