Cleaning up Your Act

When designing user applications it is important to simply the user interface whenever possible.

Excel provides facilities to turn off many of the objects, such as gridlines, scroll bars etc, that normally appear on an Excel screen.

We will use these facilities to change

into

Changing Excel's Screen

First let's do this manually, then we will write a macro to carry out the task.

Step 1 From the menu select

Tools Options View

Step 2 Now disable several of the screen elements by clicking on the check boxes. Press OK to see the effect.

Step 3 Reverse the process by selecting

Tools Options View

and clicking on the check boxes of the screen elements you want displayed.

Write a Macro to do it

Record a macro called CLEANUP by selecting Tools Record Macro Record New Macro

Now repeat Steps 1 and 2 above

Stop the recording

Tools Record Macro Stop Recording

Repeat Step 3 above to return the screen to its former state.

Save the file and test the macro.

You should now build a second macro, called RESTORE, to automate Step 3, the restoring of the screen.

Changing the Window Name

Lets Change the name of the Window

Add the line

Application.Caption = "My Application" ' set caption to the start of the 'Cleanup' macro.

Again it is good housekeeping to clean up so adjust the 'Restore' macro.

Add the line

Application.Caption = "Microsoft Excel"

to the start of the 'Restore' macro

In the next section we will examine how we can run these macros whenever Excel loads a worksheet.

---