Excel VBA Level 3

Revision of Level 2

Speeding up your macros
· Turning off screen updating
­ Using the status bar to tell the user about your progress
· Turning off automatic calculation
· Turning off Alert Messages
· Simplifying Object References
· Declaring your variable types
· Using the with-end with structure

Forms
· Showing and hiding
· Designing
­ Using option buttons
­ Using check boxes
­ Using multi pages
­ Using other controls
­ Showing / hiding options
­ Adding pictures
­ Ref Edit (so a user can select a range on a sheet)
­ Using a spin button
· Setting the tab order
· Adding OK and Cancel buttons
· Controlling data entered into a form
· Understanding Events
· Assigning selections to variables
· Unloading
· Using a form as a progress indicator

Useful features
· Using the status bar as a Progress Indicator
· Password protecting your VBA code

Creating a Toolbar
· Manually
­ Adding in useful built-in buttons
­ Adding buttons for macros
­ Customising the button
­ Attaching a toolbar to a workbook
· Using VBA
­ Why?
­ Creating / deleting a toolbar
­ Showing the toolbar
­ Placing the toolbar
­ Adding Buttons
­ Customising the buttons
­ Resetting all in-built toolbars
­ Preventing any design changes to your toolbar

Creating a Menu using VBA
· Creating / deleting a menu
· Placing the menu
· Adding Buttons
· Customising the buttons
· Resetting in-built menu

Working with Shortcut menus
· Adding / Deleting menu items to a shortcut menu
· Disabling a shortcut menu
· Resetting a shortcut menu