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: 5
  • Excell_Error.png
    Excell_Error.png
    68.7 KB · Views: 5

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,819
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Benners

New Member
Joined
Jan 17, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,819
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
 
Solution

Benners

New Member
Joined
Jan 17, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
 

Benners

New Member
Joined
Jan 17, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,819
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
There is also a TooltipText property for the control.
 

Benners

New Member
Joined
Jan 17, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,754
Messages
5,574,040
Members
412,565
Latest member
roberttaekim
Top