Changing workbook appearance focuses on changing the appearances of the worksheet cells, the worksheets of a workbook, the row and column headings, the sheet tabs, the layout view of a worksheet, the status bar, the formula bar, the Ribbon, and the Cell context menu. Figure 1 shows the components that will undergo certain changes in their appearances. In this menu, almost half of it covers on how to customize the Ribbon.
Figure 1: The discussed components in this menu that will undergo certain changes in their appearances.
The main purpose of changing the appearance is to let the users to have a better working experience when they are using your program. The changes may affect from just a worksheet cell, a range of cells, and a worksheet to the entire active workbook and every opened workbook. To see how the code affects the appearance, simply copy and paste the code into the Immediate window, a VBA module, or Custom UI Editor, and run the VBA code (or step through the VBA code by using the debugger in VBE) or reopen the workbook file (that stores the code) in Excel.
To facilitate the process of copying the code in this menu and pasting it into VBE and Custom UI Editor, you may download the code from here.
In discussing the VBA code, this site does not list all the possible built-in constants, methods and properties of the discussed objects. Rather it lists samples of commonly used constants, methods and properties with some short explanations (and with hints on how to explore further) to illustrate the ideas in accomplishing certain tasks. You can then easily find others by exploring the Object Browser window and the VBA Help system.
The items in this menu only focus on changing the appearances in Excel 2007-2016 for Windows. All the VBA code and XML code were tested in Excel 2007, 2010, and 2016. And the code should work in Excel 2013 too. The screenshots were taken from Excel 2016. The items do not discuss the workings on cells, sheets, workbooks, and files. Only in the coming menus in this site, you will encounter VBA code in, for examples, finding the last row and column of a named range, auto-filling a range, conditional formatting a range of cells, saving and retrieving the settings of your programs in the Windows Registry, copying between worksheets and workbooks, and automating the Outlook and Internet Explorer applications.
The last item in this drop-down menu is a sample program that adopts most of the discussed ideas. You may download the program for Excel 2007 and Excel 2010-2016 to see how it changes the appearance of a workbook.
Table of Contents
Changing workbook appearance
Font
Alignment
Fill with color
Border line style, color, and weight
Number format
Hide contents
Hide and unhide gridlines
Color
Hide and unhide the sheet tabs
Color a sheet tab
Hide and unhide the status bar
Write to and read from the status bar
Reset the status bar
Normal view
Page break preview
Page layout view
Window views
Maximize, minimize, restore, and display the active window in full-screen mode
Position and size the active window
Center the active window
Split the active window into panes
Freeze the split panes of the active window
Zoom slider
Zoom in and out
Scroll bars
Hide and unhide the scroll bars
Scroll a row and scroll a column
Set the scroll area
Rows in a worksheet and a macro sheet
Hide and unhide the row heading
Hide and unhide rows
Height
Columns in a worksheet and a macro sheet
Hide and unhide column heading
Hide and unhide columns
Width
Formula bar
Hide and unhide the formula bar
Height
Names in the Name box
Hide names
Minimize the Ribbon
Execute commands not in and in the Ribbon
Activate a Ribbon tab in two different ways
Identify the names (idMsos) of built-in controls, groups, tabs, tab sets, and context menus
Identify the names (imageMsos) of predefined images for controls
Add built-in controls to the Ribbon
Add custom controls to the Ribbon
Hide and unhide controls, groups, and tabs
Disable and enable controls, groups, and tabs
Repurpose certain built-in controls
Monitor a built-in control
Add a dynamic menu
Retain the ticked condition of a custom check box
Cell context menu (in Excel 2010 and later)
Add a button control
Add other types of controls
A sample program
Dissecting Excel VBA 