Hiding the look of excel

JCG82

Board Regular
Joined
May 21, 2008
Messages
101
Does anyone happen to have the link to the mrexcel podcast where he was explain how to "hide the look of excel".

I have put together a spread sheet that contains many clickable buttons that redirect the user to different pages and I would like to change the look of things just a little.

Thanks for any idea's!
 

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.
Welcome to the Board!

Here's some sample code that you can play with:

<font face=Tahoma><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#007F00">'   Set a Global Password</SPAN><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Const</SPAN> PWORD <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = 643153<br><SPAN style="color:#00007F">Public</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br><br><SPAN style="color:#00007F">Sub</SPAN> ProtectAll()<br><SPAN style="color:#007F00">'   Use this if you want to open to a specific worksheet each time</SPAN><br><SPAN style="color:#007F00">'   And uncomment the sheethidden code below</SPAN><br><SPAN style="color:#007F00">'    Sheets("SHEETNAME").Visible = True</SPAN><br><SPAN style="color:#007F00">'        Application.Goto Reference:="REFERENCENAME"</SPAN><br><SPAN style="color:#007F00">'   Use this instead of Selecting</SPAN><br><br>    <SPAN style="color:#00007F">With</SPAN> Application<br>        <SPAN style="color:#007F00">'   Renames Excel</SPAN><br>        .Caption = "Microsofa Excel"<br>        <SPAN style="color:#007F00">'   Macro Shortcut key</SPAN><br>        .MacroOptions Macro:="Protect", Description:="", ShortcutKey:="e"<br>        <SPAN style="color:#007F00">'   Turn off ScreenIUpdating</SPAN><br>        .ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#007F00">'   Turn off DisplayAlerts</SPAN><br>        .DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#007F00">'   Disable Status Bar</SPAN><br>        .DisplayStatusBar = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#007F00">'   HideFormula Bar</SPAN><br>        .DisplayFormulaBar = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#007F00">'   Disable Move After Return</SPAN><br>        .MoveAfterReturn = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br>    <SPAN style="color:#00007F">With</SPAN> ActiveWindow<br>        <SPAN style="color:#007F00">'   Hide Row & Column Headings</SPAN><br>        .DisplayHeadings = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#007F00">'   Hide Scroll Bars</SPAN><br>        .DisplayHorizontalScrollBar = <SPAN style="color:#00007F">False</SPAN><br>        .DisplayVerticalScrollBar = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#007F00">'   Turn Off Gridlines</SPAN><br>        .DisplayGridlines = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#007F00">'   Do not display Zeros</SPAN><br>        .DisplayZeros = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#007F00">'   Hide Workbook Tabs</SPAN><br>        .DisplayWorkbookTabs = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br>        ws.Activate<br>    <SPAN style="color:#007F00">'   Protect Each Sheet & Hide if not the "Master Sheet" - Named Dashboard here</SPAN><br>            ActiveSheet.Protect Password:=PWORD<br>            <SPAN style="color:#00007F">If</SPAN> ws.Name <> "Dashboard" <SPAN style="color:#00007F">Then</SPAN> ws.Visible = xlSheetVeryHidden<br>    <SPAN style="color:#00007F">Next</SPAN> ws<br><br><SPAN style="color:#007F00">'   This disables all of the Toolbars</SPAN><br><SPAN style="color:#007F00">'   Make sure to set it to TRUE BEFORE exiting Excel or you'll need the UNFUBAR file!</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> CommandBar<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> x <SPAN style="color:#00007F">In</SPAN> Application.CommandBars<br>        x.Enabled = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> Unprotect()<br><SPAN style="color:#007F00">'   Use this if you want to open to a specific worksheet each time</SPAN><br><SPAN style="color:#007F00">'   And uncomment the sheethidden code below</SPAN><br><SPAN style="color:#007F00">'    Sheets("SHEETNAME").Visible = True</SPAN><br><SPAN style="color:#007F00">'        Application.Goto Reference:="REFERENCENAME"</SPAN><br><br>    <SPAN style="color:#00007F">With</SPAN> Application<br>        <SPAN style="color:#007F00">'   Renames Excel</SPAN><br>        .Caption = "My Excel"<br>        <SPAN style="color:#007F00">'   Macro Shortcut key</SPAN><br>        .MacroOptions Macro:="Unprotect", Description:="", ShortcutKey:="r"<br>        <SPAN style="color:#007F00">'   Turn On ScreenIUpdating</SPAN><br>        .ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#007F00">'   Turn On DisplayAlerts</SPAN><br>        .DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#007F00">'   Enable Status Bar</SPAN><br>        .DisplayStatusBar = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#007F00">'   Show "Ready" comment</SPAN><br>        .StatusBar = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#007F00">'   Show Formula Bar</SPAN><br>        .DisplayFormulaBar = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#007F00">'   Enable Move After Return</SPAN><br>        .MoveAfterReturn = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br>    <SPAN style="color:#00007F">With</SPAN> ActiveWindow<br>        <SPAN style="color:#007F00">'   Show Row & Column Headings</SPAN><br>        .DisplayHeadings = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#007F00">'   Show Scroll Bars</SPAN><br>        .DisplayHorizontalScrollBar = <SPAN style="color:#00007F">True</SPAN><br>        .DisplayVerticalScrollBar = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#007F00">'   Turn On Gridlines</SPAN><br>        .DisplayGridlines = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#007F00">'   Display Zeros</SPAN><br>        .DisplayZeros = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#007F00">'   Show Workbook Tabs</SPAN><br>        .DisplayWorkbookTabs = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br>        <SPAN style="color:#007F00">'   Unprotect Each Sheet</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> ws<br>            .Visible = <SPAN style="color:#00007F">True</SPAN><br>            .Unprotect Password:=PWORD<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> ws<br><br><SPAN style="color:#007F00">'   This enables all of the Toolbars</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> CommandBar<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> x <SPAN style="color:#00007F">In</SPAN> Application.CommandBars<br>        x.Enabled = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> UnFubar()<br><br>    <SPAN style="color:#00007F">With</SPAN> Application<br>        .DisplayStatusBar = <SPAN style="color:#00007F">True</SPAN><br>        .DisplayFormulaBar = <SPAN style="color:#00007F">True</SPAN><br>        .CommandBars("Worksheet Menu Bar").Enabled = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br>    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> CommandBar<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> x <SPAN style="color:#00007F">In</SPAN> Application.CommandBars<br>            x.Enabled = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Note the UNFUBAR code at the bottom for if you forget to renable command bars, which goes along with the caveat that messing with user's settings may get you some grief, especially if you have some advanced users.

To most users the code above will be seamless though.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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