Hide Show Excel Toolbars using a Toggle Button

I currently have two macros, one to remove the excel toolbars, another to show them.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The toolbars are removed for this workbook when the workbook is opened and they are restored when the workbook is closed by using the following Workbook Activate code placed in 'This Workbook'

Please Login or Register  to view this content.

How can I convert this into a toggle 'shape' button, so that it allows the user to toggle between all toolbars hidden and all toolbars shown with the button text changing between 'Hidden' and 'Shown' accordingly.

Also, is there a way to be selective as to which toolbars are hidden and shown? For example if I want all the toolbars to be hidden apart from the status bar or all apart from the formula bar etc, how would I do this?

Many thanks

Hi Steffan,

Many thanks. I'm using a shape (Insert/Shape), so I adapted this slightly to

Please Login or Register  to view this content.

How would I add to this code to toggle the text displayed on the button between 'Show' and 'Hide', bearing in mind it is a shape rather than a command button?

Steffan,

Many thanks, that works perfectly...

Hi Steffan,

How could I expand on this. I'd like to hide all the toolbars when the sheet opens, kepping the option to toggle them all on or off, but using additional shape toggle button, allow the user to show/hide individual toolbars, e.g. the status bar or the ribbon or the formula bar etc.

I tried this, but it doesn't work.

Please Login or Register  to view this content.

Many thanks...

Okay, three questions...

Question 1
I placed this code in 'This Worksheet', is this the correct place for it and is this correct to restore all the toolbars on exiting the workbook?

Question 2
How do I stop any other workbook opened at the same time as this one from having the toolbars hidden. I would need this to be specific to this workbook only.

Please Login or Register  to view this content.

Question 3
I can create a button using this code to then toggle the 'Staus Bar' on and off:

Please Login or Register  to view this content.

or the Formula bar on and off:

Please Login or Register  to view this content.

or the Tabs on and off:

Please Login or Register  to view this content.

But how would I create a second button to toggle the 'Ribbon' on and off, as this doesn't work?

Please Login or Register  to view this content.

I'm new to VBA, so I need some hand holding in terms of where the code needs to go etc.

Many thanks...

So, changing the code to this:

Please Login or Register  to view this content.

where Test 1.xlsm is the name of the workbook, doesn't stop any other workbooks opened whilst Test 1.xlsm is open from having it's toolbars hidden. What am I doing wrong?

Like I said, I am new to VBA, so I'm not sure when I'm putting code in the wrong place...

Many thanks

It seems for the Ribbon, if it is shown when the sheet is opened, your code will hide it on the first button click but it won't re show it as a toggle and vice versa, if the ribbon is hidden when the worksheet is opened, the first button click will show it, but again it doesn't toggle?

Hi Steffan,

That fixes the toggle button, which now correctly toggles the ribbon on and off, however, I still have the issue where, if the ribbon or any of the toolbars are hidden, they are also hidden when I open any other workbooks. I tried your suggestion of creating a check on the workbbok name, but this doesn't seem to work for me.

Am I doing something wrong?

Please Login or Register  to view this content.

So, I tried this which still doesn't work, are you able to amend the sample attached rather than us going back and forward as I'm clearly missing something!

Please Login or Register  to view this content.

Many thanks

Hi Steffan,

A slight hiccup. Having this code on the 'ThisWorkbook' means that as soon as I switch to any other tab in the same Workbook, I lose the ribbon and formula bar etc.

Please Login or Register  to view this content.

Would I simply remove the code from 'ThisWorkbook' and apply it to just the 'Worksheet' I want the changes to affect and if so, how should I tweak the code?

Also, if I were to do this, I assume the toolbars wouldn't be reset on exting the Workbook, is that correct? How would I overcome that problem?