·
Advantages & Disadvantages
of using Excel Names.
·
Saving Strings, Formulas, Range
references
·
Understanding Scope of Excel
Names
·
Various ways of adding Names
·
Deleting, Modifying , Printing
& Hiding Names
·
Adding Arrays of data to Name
·
Examples:
o
Creating dynamic range
o
Adding Data Validation using
Name
CHAPTER 2
·
Understanding Events
·
Difference between Open and
Activate Event
·
Difference between Change and
Selection Change Event.
·
Example:
o
Disabling double click, right
click, Printing etc.
o
Assigning Shortcut keys to
Macros
o
Automatically closing Excel
book after given time.
o
Adding Footer before printing
o
Changing File Name when the
Cell Value is changed
o
Creating a stronger &
comprehensive Data Validation.
o
Changing Name of Sheet when
Cell Value is changed
o
Auto Numbering
o
Creating a restricted area
o
Making unauthorized editing
impossible
o
Adding to context menu
o
Creating a Security Sheet.
o
Various Observation Examples
CHAPTER 3
·
Creating user form and adding
various controls to it.
·
Making buttons responding to
Enter and Esc Keys
·
Making Command button ALT key
receptive
·
Rules for naming controls
·
Effortlessly creating controls
·
Validating data in user form
·
Making User form easy to
navigate
·
Removing user form from the
memory
·
Using Labels, Textbox, Command
button, Combobox,
·
Adding Calendar
·
Adding Pictures (Static &
Dynamically)
·
Example: Create a user form
with Add, Clear, Delete, Edit, Find & Exit Functions
·
Adding Listbox at designtime
·
Adding Listbox at runtime
·
Knowing which single item is
selected
·
Adding Table to the list
·
Extract selected item from
Multicolumn listbox
·
Extract all selected items from
Multicolumn listbox
·
Selecting Listbox items as per
selection in Combobox
·
Multiline textbox and various
features
CHAPTER 4
·
Adding Chartsheet
·
Adding a datasource to it
·
Changing location
·
Difference between Chartsheet
and Embeded charts
·
Different methods of creating
embedded chart in 2003 & 2007
·
Assessing Chart converted to
ChartObject (Excel 2003)
·
Positioning embedded Chart
·
Using Plot Area
·
Changing Charttype
·
Controlling Series
·
Copy/Pasting the Chart
·
Performing given task on all
charts in the Sheet
·
Adding Secondary Axis to Chart
·
Exporting Chart as picture
CHAPTER 5
·
Positioning Shapes
·
Selecting all shapes
·
Inserting various shapes
·
Adding Colors & formats to
shapes
·
Connecting one shape with
another.
·
Programmatically adding a Combo
box etc form controls & adding a list and linking it will cells.
·
Performing other task when
macro is running.
·
Animating Shapes
·
Inserting Pictures
·
Using Wordart
·
Copying Shapes
·
Copying shapes as picture
·
Changing Default property of
shapes
·
Performing Adjustments with
shapes
·
Inserting Text
·
Manipulating, formatting etc
few characters in the text
CHAPTER 6
·
Advantages and usage of Advance
Filter
·
Getting unique data in One
Column
·
Getting unique data from series
of columns
·
Creating Summary Report
·
Extracting data by criteria
from database
·
Creating dynamic filtering
template
CHAPTER 7
·
Understanding the concept of
Pivot table programming
·
Clearing all Pivot tables from
sheet
·
Creating Pivot Table
·
Adding Row, Column, Page &
Data Field.
·
Setting the position of ,
Column, Page & Data Field
·
Adding Calculated Fields
·
Adding/Removing subtotals
·
Performing actions on all Pivot
Fields
·
Speeding up the Code
CHAPTER 8
·
Understanding concept of
Toolbars, Menu bars & Controls.
·
Adding & Deleting Tool bar
·
Assessing Controls
·
Inserting Menubars and adding
various controls to it.
·
Adding a picture to control
·
Assigning a Macro to control
·
Creating Submenus
·
Using Combobox in controls
·
Adding controls to right click
·
Enabling/Disabling Controls
·
Adding Menu & Controls for
Addins.
·
Best Practices
CHAPTER 9
·
Using simple technique to get
work done with other applications.