Reset CommandBar references

Softlinksys

New Member
Joined
Jan 23, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
I have written some code that adds 2 new entries to the Cell right mouse click context menu. This works fine. However, I have found a spreadsheet where the menu references for the right mouse click on a cell appear to be messed up. I have added some code (courtesy of Ron de Bruin's website) that adds a button onto each CommandBar that shows its name and index value. So, on most Worksheets if you right mouse click in a Cell you see the added button that says "Cell 39". It is the CommandBar called Cell and it has Index 39. I have one workbook in which if you do this you get a Commandbar called Query with and Index of 63. If I Right click on the corner selecting the whole sheet I get the Cell 39 toolbar (which I think is correct). If I open a new sheet in the same Workbook, everything is correct... the cell context menu is Cell, the corner context menu is also Cell. Clearly, the right mouse click on a cell in Sheet1 is giving me the wrong toolbar. I need a way to fix this in VBA, so any spreadsheet I open will have the right menu associated with the right mouse click on a cell, otherwise my 2 extra buttons won't show! I will be very grateful if anybody can help me with this. By the way, I am using a Personal.xlsb file with an Auto_open routine so the code will always run at start-up.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Softlinksys

New Member
Joined
Jan 23, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Ok, I have finally sorted it. Firstly there are 2 CommandBars called "Cell" and I needed to add my extra buttons to both. One of the spreadsheets was derived from a query on the data in Outlook Contacts. So a Right mouse click on a cell gave me the "Query" CommandBar which enabled me to go into an entry called Data Range Properties. By unticking the Save Query Definition box, and accepting the warning that it would remove query definition. The right mouse click menu returned to the "Cell" CommandBar and like magic my new items were there. OK, my head has stopped hurting now.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,263
Messages
5,600,593
Members
414,391
Latest member
Pandurang

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
Top