Lock Cells with Specify Range If Cells contains value

[bernardng]

Hi,

I have data in Range("A23")inside Range("A216"), when button is pressed this range should be locked. and Range("A416") should unlock.

Everytime update button is pressed, the program should check Range("A216"), and count which rows contain data, if row 1 & 2 contain data it should be locked, the rest should be unlock.

Rgds
Bernard

 

[Loomah]

Hi

With Range("A1:A15")
    .Locked = False
    .SpecialCells(xlCellTypeConstants).Locked = True
End With

Just change the range to suit. This assumes your data contains only constants. If it also contains formulas have a look at SpecialCells in help to see your other options.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[bernardng]

Dim CurrentCell As String
    If WorksheetFunction.CountA(Range("A2:D16")) = 0 Then
    Else
    Range("D1").End(xlDown).Select
    CurrentCell = Range(ActiveCell.Address).Address
    CurrentCell = ActiveCell.CurrentRegion.Address

Bernard say: 

'CurrentCell( = Range("A1:E3")) - this range I need to lock with password.
'The rest of the cell in unlock state should be Range("A4:A16"). 
' I have using below method to Locked Cell and Worksheet, but it involved a lot o Locked and UnLocked Cell.
' The problem i have found is lock and unlock cell range is not fix, is changeable. I cannot hardcoding the cell lock range or unlock cell range.


   Worksheets("Oustanding Matter").Protection.AllowEditRanges.Add _
    Title:="Test_Lock1", Range:=Range(CurrentCell), Password:="excelvba"
        
    Worksheets("Oustanding Matter").Protect DrawingObjects:=True, Contents:=True, _
    Scenarios:=True, AllowFormattingCells:=True, AllowFormattingRows:=True

End If