what is the best way to lock and unlock cells in excel for mac

I want to know if there is a way in which if a user selects a value from the drop down Area of change the appropriate column with the same header is unlocked for editing and others are closed for editing.

The first time it runs it should lock all cells except those in the column area of change and the cell in the column referenced in Area of Change.

As values in Area of change are updated the procedure would need to be rerun.

As stated by you, it locks all the cells at the first instance.

However, is it possible to run the macro at the background and does not need to be run by clicking or requesting it to be executed the sheet would be filled by users and would be returned for updating purposes.

Please correct me if I had followed you properly to execute the script.

IN VBA, I paste the following code.

Function CellsInRangeHaveData(rInput As Range) As Boolean check att cells in the input range set True if all contain values Dim rcell As Range If Not rInput Is Nothing Then CellsInRangeHaveData True For Each rcell In rInput If IsEmpty(rcell) Then CellsInRangeHaveData False Next rcell End If End Function 3.

The Private Sub WorksheetChange(ByVal Target As Range) fires everytime a cell on sheet1 is updated, however it will not lock unlock specific cells whilst one or more cells in columns A to M for the row just updated are empty.

Once all 13 cells in the row (columns A to M) are not empty, the cells are locked with the exception of Area of Change (column H) the cell it references Please retry, Steve.

I want the users to fill the application name and based on which other columns will be automatically filled till Area of change and upon selection only the to field needs to open for editing.

No code in the.xls file you posted.

I have updated with the function and the on event code The cells with formulae in them should be locked The worksheet must be protected Once a value is entered into Application Name, the vlookup fields are completed and all cells in the row are locked except Area of change and Justification.

When the area of change cell is updated the appropriate cell is unlocked for data entry I had to add some dummy values in the data validation lists.

However, if all the mandatory fields from row 1 to row 7 are updated including Area of change all the cells are locked and the code does not allow the user to modify the column Application Name.

Similarly, the below rows are also locked which does not user to update details as the worksheet is also locked.

I tried my best to modify the codes and understood if Area of change is not entered, a user can change Application Name.

However, once Area of change is filled a user cannot change it or can delete it.

However, if i apply the code above stated, following happens.

Row 11 when filled with data (given the fact that i cannot edit once entered) the next row is automatically locked and does not let me update information.

I dont know if im doing something incorrect but it does not work in the second row.

Unprotect the worksheet unlock A11:A50 ensure B11:G50, I11:I50, K11:K50, O11:O50 all have formula and are locked Protect the worksheet Reason is that A11:A12 remain locked from previous versions of the script Good luck.

By continuing to browse this site, you are agreeing to our use of cookies.

If you would like to use all features of this site, it is mandatory to enable JavaScript.

What Is The Best Way To Lock And Unlock Cells In Excel For Mac