Lock a cell using vba

leo3487 0
Viewed 27

How to lock a cell using VBA? I saw to lock cells you need protect the sheet, but it lock all cells I want lock 1 cell my code

Private Sub Workbook_Open()
    If Environ$("computername") = "09-PC-0154" Then PCdeposito = "F:" Else PCdeposito = "\\09-PC-0154"
    Ruta = Me.Path
    If Me.Name = Form62 Then
        Worksheets("Hoja1").bNuevo.Enabled = True
        Worksheets("Hoja1").Vale.Enabled = True
        Limpiar
    Else
        Worksheets("Hoja1").bNuevo.Enabled = False
        Worksheets("Hoja1").Vale.Enabled = False
        Worksheets("Hoja1").Range("H2").Locked = True
        NombreAnterior = Worksheets("Hoja1").Range("E11").Value
        Worksheets("Hoja1").Protect
    End If
    LeerArchivoFichas
    LeerArchivoAgentes
    LeerArchivoLegajos
    Worksheets("Hoja1").Range("A1").Select
End Sub

Idea is ask if Im opening a new book or an existing numered book

Anyway the relevant for this question is the Else part

I want lock cell H2 then lock it and next line protect the sheet

But then code stop with error as with protected sheet, later lines after the IF cant work (as those functions need write at other cells)

And if no protect the sheet, the line Worksheets("Hoja1").Range("H2").Locked = True has no effect and I can write at H2

(Also I suspect if protect the sheet, users cant write at other cells)

vba excel
1 Answers

A cells 'locked' status does absolutely nothing - until the sheet is protected.

By default, all cells are locked - but without protecting the sheet those locks are useless.

But, when you protect a sheet, those locks take effect - meaning you're unable to alter those cells.

So if you unlock a cell, before protecting a sheet - just that cell is editable after protection is applied.

So if you want to lock just one cell, the most straight-forward method is:

  • remove sheet protection
  • unlock all the sheet's cells
  • lock the cell that you want protected
  • apply sheet protection

You can also dictate whether locked cells, unlocked cells or both can be selected - when applying your sheet protection.

CLR0
Related