Lock certain cells

Question

Hello!

How would I lock the first 6 options on the top of the form and make those REQUIRED fields before being able to complete the rest of the form?

Also, is Excel able to just allow a certain section available once the top part is completed to be available? Essentially I don't want the whole rest of the form available quite yet, there would still be 2 more sections of (checking boxes and selecting a Change Reason from drop down list) that they would need to do before the entire rest of the form is available.

Basically, these sections tend to get skipped over so just trying to eliminate that by those completing the form to only have the available sections that are required able to be edited.

Hope this makes sense!

Answer 1

Yep - Excel can enforce this, but the way you do it depends on how strictly you want to control access.

If you want the first six fields to be required before anything else can be completed, combine sheet protection with Data Validation.

First, lock the entire sheet. Select the whole sheet with Ctrl + A, right-click, choose Format Cells, go to the Protection tab, and ensure Locked is checked. Then select only the first six required cells, go back to Format Cells → Protection, and uncheck Locked. After that, protect the sheet from the Review tab, allowing only “Select unlocked cells.” This ensures users can only edit those six cells initially.

Next, create a helper cell somewhere out of the way, for example Z1, with this formula:

=COUNTBLANK(A2:F2)=0

Adjust A2:F2 to match your required fields. This formula returns TRUE only when all required cells are filled.

Now select the next section of the form that should only be available after the first six fields are complete. Go to Data → Data Validation → Allow: Custom, and use this formula:

=$Z$1=TRUE

Set the Error Alert style to Stop and enter a message such as “Complete the top section before continuing.” This prevents users from entering anything in that section until the required fields are filled.

You can repeat the same concept for a third section. For example, if Section 2 must also be complete before Section 3 becomes available, create another helper cell such as Z2:

=AND($Z$1=TRUE, COUNTBLANK(A5:D10)=0)

Then apply Data Validation to Section 3 using:

=$Z$2=TRUE

This creates a progressive unlocking effect where each section becomes usable only after the previous one is completed.

If you want sections to actually unlock automatically rather than just block entry, VBA is required. In the worksheet code module, you could use:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim topComplete As Boolean
    topComplete = Application.WorksheetFunction.CountBlank(Range("A2:F2")) = 0
    
    Application.EnableEvents = False
    Me.Unprotect Password:="1234"
    
    If topComplete Then
        Range("A5:D20").Locked = False
    Else
        Range("A5:D20").Locked = True
    End If
    
    Me.Protect Password:="1234"
    Application.EnableEvents = True

End Sub

You would adjust the ranges to match your layout. This physically locks and unlocks sections based on completion of the required fields.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin