Transcript Chapter 12

by Mary Anne Poatsy, Keith
Mulbery, Eric Cameron, Jason
Davidson, Rebecca Lawson,
Linda Lau, Jerri Williams
Chapter 12
Templates, Styles,
and Macros
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
1
• Select a template
• Apply themes and backgrounds
• Apply cell styles
• Create and use a template
• Protect a cell, a worksheet, and a
workbook
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
2
• Create a macro
• Create macro buttons
• Set macro security
• Create a sub procedure
• Create a custom function
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
3
• Template—partially completed document used
as a model to create other documents
• Contains
– Standard labels
– Formulas
– Formatting
• May contain little or no quantitative data
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
4
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
5
• Theme—collection of formats
– Coordinating colors
– Fonts
– Special effects
• Gives a consistent look with other workbooks
used in a department or organization
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
6
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
7
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
8
• Cell style—collection of format settings based on
the current theme
• Provides a consistent appearance within a
worksheet and among workbooks
• Control:
–
–
–
–
–
Font attributes
Borders and fill styles and colors
Vertical and horizontal cell alignment
Number formatting
Cell-protection settings
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
9
• Cell Styles gallery predefined cell categories:
– Good, Bad, and Neutral
– Data and Model
– Titles and Headings
– Themed Cell Styles
– Number Format
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
10
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
11
• Template creation guidelines:
– Should contain formatted, descriptive labels, empty
cells, and formulas
– Avoid values in formulas; use cell references instead
– Include data-validation settings
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
12
• Template creation guidelines (cont.):
– Include template instructions
– Apply appropriate template formatting
– Give worksheets meaningful names
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
13
• Locked cell— prevents users from editing the
contents or formatting cells in a protected
worksheet
• Cells are not locked until the worksheet is
protected
• Locking/unlocking cells has no effect if the
worksheet has not been protected
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
14
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
15
• Protecting a worksheet protects only the
worksheet
• Protect the entire workbook to prevent users
from making unwanted changes
• Use a password to further protect against
changes
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
16
• Macro—instructions that execute commands to
automate repetitive or routine tasks
• Two methods
– Using the Macro Recorder
– Typing instructions using Visual Basic for
Applications (VBA)
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
17
• Macro Recorder—records commands,
keystrokes, and mouse clicks
• Macros not stored in Excel Workbook file
format (.xlsx)
• Macros stored:
– Excel Macro-Enabled Workbook (.xlsm)
– Excel Binary Workbook (.xlsb)
– Excel Macro-Enabled Template (.xltm)
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
18
• Accessing Macro Recorder:
– From the View tab
– From the Developer tab
– From the status bar
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
19
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
20
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
21
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
22
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
23
• Visual Basic for Applications—programming
language to enhance and automate functionality
• Visual Basic Editor:
–
–
–
–
Create
Edit
Execute
Debug Office application macros
• VBA macros:
– Sub procedures
– Custom functions
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
24
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
25
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
26
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
27
• Workbooks can be standardized by
using:
– Templates
– Themes
– Styles
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
28
• Protection can be applied to:
– Cells
– Worksheets
– Workbooks
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
29
• Macros can be created and assigned to
buttons
• Macro security settings can be changed using
the Trust Center dialog box
• Sub procedures and custom functions are
created using VBA
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
30
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
31
All rights reserved. No part of this publication may be reproduced, stored in a retrieval
system, or transmitted, in any form or by any means, electronic, mechanical, photocopying,
recording, or otherwise, without the prior written permission of the publisher. Printed in the
United States of America.
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
32