Excel Tutorial 8 Developing an Excel Application - PowerPoint PPT Presentation

Loading...

PPT – Excel Tutorial 8 Developing an Excel Application PowerPoint presentation | free to download - id: 9bce0-OTQyM



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Excel Tutorial 8 Developing an Excel Application

Description:

Create, edit, and delete defined names for cells and ranges ... Enter a name for the macro, and specify the location to store the macro ... – PowerPoint PPT presentation

Number of Views:125
Avg rating:3.0/5.0
Slides: 27
Provided by: course166
Learn more at: http://oc.course.com
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Excel Tutorial 8 Developing an Excel Application


1
Excel Tutorial 8Developing an Excel Application
2
Objectives
  • Create, edit, and delete defined names for cells
    and ranges
  • Paste a list of defined names as documentation
  • Use defined names in formulas
  • Add defined names to existing formulas
  • Create validation rules for data entry
  • Protect the contents of worksheets and workbooks

3
Objectives
  • Add, edit, and delete comments
  • Learn about macro viruses and Excel security
    features
  • Create a macro using the macro recorder
  • Edit a macro using the Visual Basic Editor
  • Assign a macro to a keyboard shortcut and a
    button
  • Save a workbook in macro enabled format
  • Minimize the Ribbon

4
Planning an Excel Application
  • An Excel application is a spreadsheet written or
    tailored to meet the users specific needs

5
Naming Cells and Ranges
  • A defined name (often called simply a name) is a
    word or string of characters associated with a
    single cell or a range
  • Select the cell or range to which you want to
    assign a name
  • Click in the Name box on the formula bar, type
    the name, and then press the Enter key (or in the
    Defined Names group on the Formulas tab, click
    the Define Name button, type a name in the Name
    box, and then click the OK button)
  • Or
  • Select the range with labels and blank cells in
    the top row or first column to which you want to
    assign a name
  • In the Defined Names group on the Formulas tab,
    click the Create From Selection button
  • Specify whether to create the ranges based on the
    top row, bottom row, left column, or right column
    in the list
  • Click the OK button

6
Naming Cells and Ranges
7
Adding Defined Names to Existing Formulas
  • In the Defined Names group on the Formulas tab,
    click the Define Name button arrow, and then
    click Apply Names (if the cell reference and
    defined name definition are in the same
    worksheet)
  • In the Apply Names dialog box, select the names
    you want to apply, and then click the OK button
  • Or
  • Edit the formula by selecting the cell reference
    and typing the defined name (or clicking the
    appropriate cell)

8
Adding Defined Names to Existing Formulas
9
Validating Data Entry
  • To ensure that correct data is entered and stored
    in a worksheet, you can use data validation to
    create a set of rules that determine what users
    can enter in a specific cell or range
  • An input message appears when the cell becomes
    active and can be used to specify the type of
    data the user should enter in that cell
  • An error alert message appears if a user tries to
    enter a value in the cell that does not meet the
    validation rule

10
Validating Data Entry
  • In the Data Tools group on the Data tab, click
    the Data Validation button
  • Click the Settings tab
  • Click the Allow arrow, click the type of data
    allowed in the cell, and then enter the
    validation criteria for that data
  • Click the Input Message tab, and then enter a
    title and text for the input message
  • Click the Error Alert tab, and then, if
    necessary, click the Show error alert after
    invalid data is entered check box to insert a
    check mark
  • Select an alert style, and then enter the title
    and text for the error alert message
  • Click the OK button

11
Validating Data Entry
12
Protecting a Worksheet
  • Select the cell or range you want to unlock.
  • In the Font group on the Home tab, click the
    Dialog Box Launcher
  • In the Format Cells dialog box, click the
    Protection tab, click the Locked check box to
    remove the check mark, and then click the OK
    button
  • In the Changes group on the Review tab, click the
    Protect Sheet button
  • Enter a password (optional)
  • Select all of the actions you want to allow users
    to take when the worksheet is protected
  • Click the OK button

13
Protecting a Workbook
  • In the Changes group on the Review tab, click the
    Protect Workbook button
  • Click the check boxes to indicate whether you
    want to protect the workbooks structure,
    windows, or both
  • Enter a password (optional)
  • Click the OK button

14
Adding Worksheet Comments
  • A comment is a text box that is attached to a
    specific cell in a worksheet
  • Click the cell to which you want to attach a
    comment
  • Right-click the cell, and then click Insert
    Comment on the shortcut menu (or in the Comments
    group on the Review tab, click the New Comment
    button)
  • Type the comment into the comment box

15
Adding Worksheet Comments
16
Working with Macros
  • A macro is a series of stored commands that can
    be run whenever you need to perform the task
  • The Developer tab will enable you to create and
    use macros
  • If the Developer tab isnt available, you will
    need to enable it using the Excel Options

17
Protecting Against Macro Viruses
  • Macro viruses are a type of virus that uses a
    program's own macro programming language to
    distribute the virus
  • The macro security settings control what Excel
    will do about macros in a workbook when you open
    that workbook
  • Disable all macros without notification
  • Disable a macro with notification
  • Disable all macros except digitally signed macros
  • Enable all macros

18
Setting Macro Security in Excel
  • In the Code group on the Developer tab, click the
    Macro Security button
  • Click the option button for the security setting
    you want
  • Click the OK button
  • or
  • Click the Office Button, and then click the Excel
    Options button
  • Click the Trust Center category, and then click
    the Trust Center Settings button
  • Click the Macro Settings category, and then
    select the option button for the security setting
    you want
  • Click the OK button

19
Recording a Macro
  • In the Code group on the Developer tab, click the
    Record Macro button
  • Enter a name for the macro, and specify the
    location to store the macro
  • Specify a shortcut key (optional)
  • Enter a description of the macro (optional)
  • Click the OK button to start the macro recorder
  • Perform the tasks you want to automate
  • Click the Stop Recording button

20
Running a Macro
  • Press the shortcut key assigned to the macro
  • or
  • In the Code group on the Developer tab, click the
    Macros button
  • Select the macro from the list of macros, and
    then click the Run button

21
Editing a Macro
  • In the Code group on the Developer tab, click the
    Macros button, select the macro in the Macro name
    list, and then click the Edit button (or in the
    Code group on the Developer tab, click the Visual
    Basic button)
  • Use the Visual Basic Editor to edit the macro
    code
  • Click File on the menu bar, and then click Close
    and Return to Microsoft Excel

22
Editing a Macro
23
Creating a Macro Button
  • In the Controls group on the Developer tab, click
    the Insert button
  • In the Form Controls section, click the Button
    (Form Control) tool, click the worksheet where
    you want the macro button to be located, drag the
    pointer until the button is the size and shape
    you want, and then release the mouse button
  • In the Assign Macro dialog box, select the macro
    you want to assign to the button, and then, with
    the button still selected, type a new label

24
Creating a Macro Button
25
Saving Workbooks with Macros
  • On the Quick Access Toolbar, click the Save
    button
  • Click No in the dialog box
  • Save as an Excel Macro-Enabled Workbook

26
Opening a Workbook with Macros
About PowerShow.com