Forum Discussion

Shaun498's avatar
Shaun498
Copper Contributor
Dec 18, 2022

Locking a column based on a cell value in the same column

Hi there. Appreciate any assitance that works. Thanks in advance

I wish to lock and unlock fixed rows in each column in a spreadsheet for any editing (including delete) based on a value at the top row of each column. 

  • Data table is A1:E10 and wish to lock rows A2-A10 Based on the value in A1 and Rows B2-B10 based on a value in B1 and so on
  • Each column relates to data for a particular date and we update the same data each day. Wish to avoid risk of person overwriting data in the previous column.
  • Intention is that if there is a "1" in the top row (a1-AA.....1) of any column then it is locked. If they wish to edit that column then they delete the "1" and have at it
excel
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Dec 19, 2022

    Shaun498 

    See the attached workbook. Since it contains VBA code (in the worksheet module), users will have to allow macros when they open it.

    The worksheet has been protected, currently without a password. If you want to set a password, you can change it in the code (right-click the sheet tab and select 'View Code' to view and edit the VBA code).

Resources