How to Make a Cell Required in Excel 2007

Written by grace grimm
  • Share
  • Tweet
  • Share
  • Email

Creating required cells in a Microsoft Excel spreadsheet is one way to ensure that important information does not go missing or get skipped. There is more than one way to make a cell required. You could add VBA code to your Excel workbook or add code that prevents the user from saving the spreadsheet until the required cells have been filled in. VBA code can be added to a spreadsheet in just a few steps and doesn't require any programming experience.

Skill level:
Moderately Easy

Other People Are Reading

Instructions

    Making Cells Required to Print Spreadsheet

  1. 1

    Open Excel 2007. Check for the "Developer" tab in the ribbon across the top of the screen. If you do not have the "Developer" tab activated, click the round blue "Microsoft Office" button in the top left corner. Click "Excel Options" followed by "Popular." Check the box for "Show Developer Tab in the Ribbon," and then click "OK."

  2. 2

    Click the "Developer" tab, and then click the "View Code" button under the "Developer" menu. This will open Microsoft Visual Basic.

  3. 3

    Copy and paste the following code into the blank window:

    [vba]Private Sub Workbook_BeforePrint(Cancel As Boolean)

    If Sheet1.Range("A1:B2").Value = "" Then

    MsgBox "Cannot print until required cells have been completed!"

    Cancel = True

    End If

    End Sub[/vba]

  4. 4

    Replace the "Sheet1" and "A1:B2" values within the code with the range of values you would like to require in your spreadsheet. For example, if you want to require the first 10 cells in column A of Sheet 2 of your spreadsheet, you would change the second line of the code to:

    If Sheet2.Range("A1:A10").Value = "" Then

  5. 5

    Close Microsoft Visual Basic. Save your Excel file to make the code a permanent part of the file.

    Making Cells Required to Save Spreadsheet

  1. 1

    Open Excel 2007 and check to see if the "Developer" tab is present in the ribbon across the top of the screen. If you don't see the "Developer" tab, click the round blue "Microsoft Office" button in the top left corner. Click "Excel Options" followed by "Popular." Check the box for "Show Developer Tab in the Ribbon," and then click "OK."

  2. 2

    Click the "Developer" tab, and then click the "View Code" button under the "Developer" menu to launch Microsoft Visual Basic. Visual Basic allows you to view and organise any code you've added to a spreadsheet, as well as write or add new code.

  3. 3

    Copy and paste the following code into the empty window within Microsoft Visual Basic:

    [vba]Private Sub Workbook_BeforeSave(Cancel As Boolean)

    If Sheet1.Range("A1:B2").Value = "" Then

    MsgBox "Cannot save until required cells have been completed!"

    Cancel = True

    End If

    End Sub[/vba]

  4. 4

    Customise the code by replacing the "Sheet1" and "A1:B2" values with the range of values you would like to require in your spreadsheet. For example, if you want to require the first 10 cells in column A of Sheet 2 of your spreadsheet, you would change the second line of the code to:

    If Sheet2.Range("A1:A10").Value = "" Then

  5. 5

    Attempt to save the Excel sheet without filling in all of the required cells. If a message box pops up reading "Cannot save until required cells have been completed!", your code is working correctly.

Tips and warnings

  • The phrase in quotes after "MsgBox" in the VBA code can be changed to whatever you would like the pop-up box to say when users attempt to save or print without filling in the required boxes.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.