Features Added in FOCUS 7.6.5

In this section:

Starting in FOCUS 7.6.5, you can lock cells in an Excel spreadsheet
Top of page
x
Excel Cell Locking

How to:

Using StyleSheet attributes, you can lock Excel spreadsheet values so they are read-only. These attributes apply to all Excel formats including EXL2K, EXL2K PIVOT, and EXL2K FORMULA.

x
Syntax: How to Enable Spreadsheet Locking

To enable locking, use the following attributes: 

TYPE=REPORT, PROTECTED={ON|OFF}, [LOCKED={ON|OFF}],$

where:

TYPE=REPORT, PROTECTED=ON

Is necessary to enable spreadsheet locking. PROTECTED=OFF is the default. If you omit the LOCKED=OFF attribute, the entire spreadsheet is locked.

LOCKED=ON

Locks the entire spreadsheet. ON is the default value.

LOCKED=OFF

Unlocks the spreadsheet as a whole, but enables you to lock or unlock specific cells or groups of cells.
x
Syntax: How to Lock Specific Cells Within a Spreadsheet

Once you include the following declaration in your StyleSheet, you can specify the LOCKED attribute for specific cells or groups of cells: 

TYPE=REPORT, PROTECTED=ON, LOCKED=OFF,$

To lock specific parts of the spreadsheet, add the LOCKED=ON attribute to the StyleSheet declaration for the cells you want to lock.

TYPE=type, [ COLUMN=columnspec ] ,LOCKED={ON|OFF},$

where:

type

Is the type of element that describes the cells to be locked.

columnspec

Is a valid column specification.
Example: Locking an Entire Excel Spreadsheet

The following request locks the entire spreadsheet because the StyleSheet declarations include the following declaration:

TYPE=REPORT, PROTECTED=ON, $

The request is:

TABLE FILE CAR                                    
HEADING                                           
"Profit By Car "                                  
" "                                               
SUM RETAIL_COST AND DEALER_COST AND                                 
COMPUTE PROFIT/D12.2 = RETAIL_COST - DEALER_COST;                   
BY CAR                                            
ON TABLE SET PAGE-NUM OFF                         
ON TABLE PCHOLD AS EXLFORM1 FORMAT EXL2K          
ON TABLE SET STYLE *                              
TYPE=REPORT, COLOR=BLUE, BACKCOLOR=SILVER, SIZE=9,$
TYPE=REPORT, PROTECTED=ON,            $           
TYPE=HEADING, STYLE=BOLD, SIZE=14, $              
TYPE=TITLE, STYLE=BOLD, SIZE=11,$                 
ENDSTYLE                                          
END

You cannot edit any value on the spreadsheet. Any attempt to do so displays a message that the sheet is protected:
Example: Locking a Single Column on an Excel Spreadsheet

The following request locks the second column (RETAIL_COST) because the StyleSheet declarations include the following declarations

TYPE=REPORT, PROTECTED=ON, LOCKED=OFF, $
TYPE=DATA, COLUMN=2, LOCKED=ON,$

The request is:

TABLE FILE CAR                                     
HEADING                                            
"Profit By Car "                                   
" "                                                
SUM RETAIL_COST AND DEALER_COST AND                                  
COMPUTE PROFIT/D12.2 = RETAIL_COST - DEALER_COST;                    
BY CAR                                             
ON TABLE SET PAGE-NUM OFF                          
ON TABLE PCHOLD AS EXLFORM2 FORMAT EXL2K           
ON TABLE SET STYLE *                               
TYPE=REPORT, COLOR=BLUE, BACKCOLOR=SILVER, SIZE=9,$
TYPE=REPORT, PROTECTED=ON, LOCKED=OFF,$            
TYPE=HEADING, STYLE=BOLD, SIZE=14, $               
TYPE=TITLE, STYLE=BOLD, SIZE=11,$                  
TYPE=DATA, COLUMN=2, LOCKED=ON,$                   
ENDSTYLE                                           
END

You cannot edit any value in column 2, although you can edit values in other columns. Any attempt to edit a value in column 2 displays a message that the cells are protected:

Information Builders