Monday, April 16, 2012

How to Make a Cell Required in Excel 2007


Making Cells Required to Print Spreadsheet
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. Click the 'Developer' tab, and then click the 'View Code' button under the 'Developer' menu. This will open Microsoft Visual Basic.
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 = '' ThenMsgBox 'Cannot print until required cells have been completed!'Cancel = TrueEnd IfEnd Sub[/vba]
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. Close Microsoft Visual Basic. Save your Excel file to make the code a permanent part of the file.
Making Cells Required to Save Spreadsheet
6. 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.'
7. 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 organize any code you've added to a spreadsheet, as well as write or add new code.
8. 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 = '' ThenMsgBox 'Cannot save until required cells have been completed!'Cancel = TrueEnd IfEnd Sub[/vba]
9. Customize 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
10. 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.

Blogger news