Help with automating cell locking for ranges

[GWRW1964]

I have a SS where I have two (unnamed) ranges.

I want it so that if ALL cells in range1 are empty, ALL cells in range2 will be locked, but if ANY cell/s in range1 are populated, ALL cells in the range2 are unlocked.

I have very limited VBA knowledge and nothing I have tried seems to work.

Thanks in advance

G

 

[Herakles]

This code may give you what you need.

Just change the sheet and range references in the subMain procedure?

Public Sub subMain()
Dim rngCheckEmpty As Range
Dim rngLock As Range

    Set rngCheckEmpty = Worksheets("Sheet1").Range("A2:B6")
    
    Set rngLock = Worksheets("Sheet1").Range("C2:F6")
    
    Call subLockOrUnlockRange(rngCheckEmpty, rngLocked)
    
End Sub

Public Sub subLockOrUnlockRange(rngCheckEmpty As Range, rngLock As Range)

    Worksheets(rngCheckEmpty.Name).Unprotect

    If WorksheetFunction.CountA(rngCheckEmpty) = 0 Then
        rngLock.Locked = True
    Else
        rngLock.Locked = False
    End If

    Worksheets(rngCheckEmpty.Name).Protect

End Sub

 

[mmhill]

You have to tell VBA what the ranges are somehow. If not by name, then by address. Then, it's easy to fire off with the Worksheet Calculate or Worksheet Change event.

If the addresses are static, in the VBA editor, paste the code below into the worksheet code module (not a regular module). Every time the worksheet calculates, it will evaluate Range1 and Lock/Unlock Range2. Replace Range1Address and Range2Address accordingly. You could also use named ranges.

Private Sub Worksheet_Calculate() 'you could use Change here as well.
    Range("Range2Address").Locked = (Application.WorksheetFunction.CountBlank(Range("Range1Address"))) = Range("Range1Address").Rows.Count
End Sub

If you want to automatically protect and unprotect , then add lines to the Calculate macro. This will unprotect/protect is the sheet starts as protect and do nothing if the sheet starts as unprotected. You can do the same with the calculate macro below.

Private Sub Worksheet_Calculate() 'you could use Change here as well.
    tmp = Me.ProtectContents: Me.Unprotect
        Range("Range2Address").Locked = (Application.WorksheetFunction.CountBlank(Range("Range1Address"))) = Range("Range1Address").Rows.Count
    If tmp Then Me.Protect
End Sub

If you want to make it easy to change, you could use a volatile custom function (it evaluates every time the worksheet is calculated) to set some public range variables. The functon returns TRUE if locked and FALSE if not. Then, the worksheet calculate event Locks/Unlocks Range2. You have to do it like this because a custom function cannot chage other cells.

In a code module paste:

Public R01 As Range, R02 As Range
Public Function LockMe(R1 As Range, R2 As Range) As Boolean
    Application.Volatile
    Set R01 = R1: Set R02 = R2
    LockMe = (Application.WorksheetFunction.CountBlank(R01)) = R01.Rows.Count
End Function

Then, in the worksheet code module paste:

Private Sub Worksheet_Calculate()
    R02.Locked = (Application.WorksheetFunction.CountBlank(R01)) = R01.Rows.Count
End Sub

In any cell on the worksheet enter the formula =LockMe([Range1 Address],[Range2 Address])