CODEWITHSUNDEEP
excelvba
Samoknight
Samoknight

Reputation: 47

Lock a range of cells in row based on first cell in row for every row in a table

Thank you in advance for your help.

The short version:

I am working on an Excel sheet that contains a table. This table is used to record key information about products and is being updated daily. Each product is defined in its own row with columns being used to show details such as "Drawing number" and "Customer" etc. Depending on the stage the product is at in development and how well the product went once completed, it will be labeled with a single "state" from a list such as "Ongoing", "Bad-product", "Archived" etc. enter image description here In each cell in each row, the text the cell contains is determined through either data validation in the form of lists or manually typed in. for example, all cells in the customer column will use a dropdown menu to select a customer from a list, but the cells in the drawing number column are always one of a kind so require manual input.

As so many people access and edit this table there are issues with mis-clicking and typing which causes data to be overwritten without someone noticing they have done so. Because of this issue I want to be able to lock specific columns in a row based on what the first cell in the row contains ("State" column).

In the instance that the state column contains the word "archived" I want to lock the entire row from having its contents being changed.

In the case of the word being "Ongoing" I want to lock column AA and AB in that row.

As it is sometimes is necessary to go back and edit information due to a known mistake I would need to have it so the State column always remains unlocked whatever it contains.

I have attached a redacted Excel sheet that contains my attempt at a coded solution. https://drive.google.com/open?id=1rzTp0ur1tpXIY_Wa3cVlcwruJ7Q1rT The code can also be seen below. Note: I was not sure how to even go about fulfilling the requirement for the "Ongoing" condition so would really appreciate it if someone could point me in the right direction

Sub Locking()

Dim KeyCells As Range
    Set KeyCells = Range("A3:A612")

For Each cell In Range("A3:A612")

    If cell.Value = "Archived" Then
        cell.EntireRow.Locked = True


    ElseIf cell.Value = "Ongoing" Then
        cell.EntireRow.Locked = False


    ElseIf cell.Value = "Bad" Then
        cell.EntireRow.Locked = False

    End If

Next cell

End Sub

This is my first post on here as I could not find the solution from looking at several different stack overflow questions. I also watched numerous videos on VBA code, but am definitely missing something, and am struggling to figure out what it is.

If I have posted this incorrectly or extra detail would be helpful, please let me know.

Any and all help is extremely appreciated!

Thank you

Edit: Code works now

Upvotes: 1

Views: 598

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

Instead of locking the entire row 

 cell.EntireRow.Locked = False

if you just want to lock AA and AB in that row do the following:

 cell.EntireRow.Locked = False 'unlock entire row first
 'then lock just the 2 rows
 Range("AA" & cell.Row & ":AB" & cell.Row).Locked = True

The others would work the same way.

Upvotes: 1

Related Questions