PDA

View Full Version : VBA code to hide ribbon / menu bar ?



bdsii
11-16-2011, 06:11 PM
All - I have a couple of goober users that want to try to meddle with a spreadsheet and code that I have created. I would like to create code to run upon open of the spreadsheet that will hide or disable the ribbon and menu bars and only allow them to enter data into the spreadsheet. I do not want them to copy or paste or anything else. Then I would like separate code to unhide or enable the ribbon and menus if I need to use it.

What I had planned was to have two pieces of code, one to hide and the other two enable. I would have the hide code run upon startup and then the enable code I would assign a CTRL letter to activate when I want to use the menus. I could then assign the code to hide the menus again to another CTRL letter so I could hide the ribbon/menus again.

Inside the code I may also want to disable the CTRL-C to prevent copying in the entire spreadsheet to also help prevent them from meddling with formulas.

Any suggestions or code that may help ? I am not sure of the code that would accomplish this. :dunno

Thanks !

Aflatoon
11-17-2011, 02:14 AM
To hide:
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"

to show, simply change False to True.

ronjon65
11-17-2011, 07:43 PM
Thanks. Is there an equivalent to this in Excel 2003?

Aflatoon
11-18-2011, 02:22 AM
There is no Ribbon in 2003 so you either have to put the application in Full Screen mode or loop through all Commandbars making them invisible.

bdsii
11-20-2011, 08:35 AM
Thanks Aflatoon.....is ExecuteExcel4Macro the name of the macro or should this remain the same in the code and then just create a module with a different name and paste this inside that module ? I appreciate the help :-)




Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"

Aflatoon
11-20-2011, 12:56 PM
That's just a line of code that goes in whatever routine you need it in.

mikerickson
11-20-2011, 01:07 PM
What happens if your other code crashes?
What happens if Excel crashes? or Windows?

How does the user get their Excel controls back?

bdsii
11-20-2011, 04:00 PM
thanks again Aflatoon.....works just as I had hoped. Now I need to figure out if it is possible to hide only one or two tabs on the ribbon via VBA. I can then have two options possibly for the goober users :-)

mikerickson - are you pointing out a problem with this approach ?

mikerickson
11-20-2011, 04:43 PM
Any dictator application has the problem that if it falls over, it can't get back up on its own.

Yours is not the only workbook these users use. The power VBA gives a developer comes with the attendant responsibility to insure that the users aren't inadvertently hurt.

Aflatoon
11-21-2011, 01:55 AM
Now I need to figure out if it is possible to hide only one or two tabs on the ribbon via VBA.

No - you need to alter the CustomUI in the workbook's XML. See Ron de Bruin's site for plenty of examples. Andy Pope also has a VBA driven editor.