Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Turning Off AutoFill for a Workbook

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Turning Off AutoFill for a Workbook.

The AutoFill feature of Excel can be a great timesaver when entering information. However, you may want to disable the feature for a particular workbook; perhaps you don't want the feature to be used by anyone using your workbook. Doing so is easy if you create a macro that can recognize when you are working with that particular workbook.

To turn off the AutoFill feature for a particular workbook, follow these steps:

  1. Open the workbook for which you want to disable the AutoFill feature. (It should be the only workbook you have open.)
  2. Press Alt+F11 to open the VBA editor.
  3. Using the Project window, select the ThisWorkbook object within the workbook you opened in step 1.
  4. Press F7. The Code window appears for the ThisWorkbook object.
  5. Within the Code window, enter the following two macros:
  6. Private Sub Workbook_Activate()
        Application.CellDragAndDrop = False
    End Sub
    
    Private Sub Workbook_Deactivate()
        Application.CellDragAndDrop = True
    End Sub
    
  7. Close the VBA editor.
  8. Save your workbook.

The first macro is only run when the particular workbook is activated. In other words, when the workbook attains focus or is selected. When a different workbook attains focus, then the second macro is executed.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2594) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Turning Off AutoFill for a Workbook.

Related Tips:

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Jeanette    29 Jun 2016, 17:10
I figured it out. Thanks. It works.
Jeanette    27 Jun 2016, 15:01
Hi,

I would like to turn off autofill for a workbook (that I plan to use in as a shared workbook.).

I did the above steps and saved it as a .xlsm file. It still autofills.

What should I do?

Jeanette
ohdjrp4    13 Apr 2013, 11:37
you can also go to 'Options' > 'Advance' > 'Editing' and remove the tick mark for 'Enable Fill Handle...
leobaba    13 Apr 2012, 16:40
I love it[the codes]
but heelp me with this. i'm creating a program on student records i've finished it but I want it in a way that will sort from A-Z and when another student name is entered it will automatically move a specific amount of cells and its contents be it formulas or activex tools downward and create a new student record.
NB_ the student names are from another workbook entirely.
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.