Vba to protect the cell

**Anil2007** · 05-27-2009, 04:00 AM

Hi I Need a help that when i click on save button the Non Blank
Cell will protect so that no one can edit the cell content .only the
Blank cell will edit

**royUK** · 05-27-2009, 04:21 AM

The default for cells is locked, so first unlock all the cells.

Click the empty grey box between the colmn & row headers to select all cells -> Format -> Protection -> uncheck Locked. Then use this code

Option Explicit

Sub Button1_Click()
    Const PW   As String = "secret"
    Dim rNonBlanks As Range
    On Error Resume Next
    Set rNonBlanks = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
    On Error GoTo 0
    rNonBlanks.Locked = True
    ActiveSheet.Protect PW
End Sub

**royUK** · 05-27-2009, 04:21 AM

The default for cells is locked, so first unlock all the cells.

Click the empty grey box between the colmn & row headers to select all cells -> Format -> Protection -> uncheck Locked. Then use this code

Option Explicit

Sub Button1_Click()
    Const PW   As String = "secret"
    Dim rNonBlanks As Range
    On Error Resume Next
    Set rNonBlanks = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
    On Error GoTo 0
    rNonBlanks.Locked = True
    ActiveSheet.Protect PW
End Sub

**Anil2007** · 05-27-2009, 05:47 AM

Originally Posted by royUK

The default for cells is locked, so first unlock all the cells.

Click the empty grey box between the colmn & row headers to select all cells -> Format -> Protection -> uncheck Locked. Then use this code

Option Explicit

Sub Button1_Click()
    Const PW   As String = "secret"
    Dim rNonBlanks As Range
    On Error Resume Next
    Set rNonBlanks = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
    On Error GoTo 0
    rNonBlanks.Locked = True
    ActiveSheet.Protect PW
End Sub

Thank for reply Roy is that possible we can define the range in code so that macro will run on that range only instead on whole sheet