Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Protect Excel Worksheet for format and size and allow only for entry

Tags:

excel

I am creating an XLS worksheet that would be used to collect data from the users. I have restricted the user input using validations. In order to easily be able to print the worksheet i have set the lenghts of the columns. Have made the relevant columns wrap. However i would like to protect the worksheet such that User is not allowed to 1. Change the format 2. Change the Validations 3. Change the column size User should be allowed to 1. Enter input values 2. Select the value (from drop down whereever applicable)

The protect sheet always restricts user inputs.

like image 806
Dheer Avatar asked Nov 06 '22 23:11

Dheer


1 Answers

The key is after you protect the sheet to use the interface exposed in "Allow Users To Edit Ranges". I'm going to assume you are using Office 2003 since you didn't specify, so you find it in Tools -> Protection -> Allow Users to Edit Ranges.

From there it should be pretty obvious - you create named ranges and give edit access to users based on that.

On the second issue of having users pick values from combo-boxes, you control that through Data -> Validation then create a Custom list.

like image 118
BKimmel Avatar answered Dec 08 '22 07:12

BKimmel