VBA/ 2007: Lock cells based on value of other cells...

[Christiaan]

Hello everyone.

I want cells to be automatically be locked, based on a value of another cell.

If there is no attendance: Lock cells 'test score, result, re-test score and re-test result'.
If there is attendance: Unlock cells 'test score, result, re-test score and re-test result'. BUT: If the test is passed, lock cells 're-test score and re-test result'.

I have an example worksheet available for download.

Any help would be greatly appreciated!!!

Warm regards,
Christiaan

 

[Christiaan]

I have found something similar, but needs to be adjusted to my sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If [B3] = "Promotion" Then
[S3].Interior.ColorIndex = 34
[S3].ClearContents
Else
[S3].Interior.ColorIndex = 0
End If
End SubPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Address <> Target.Address Then Exit Sub
If Target.Address = "$S$3" Then
If [B3] = "Promotion" Then
[S3].ClearContents
MsgBox "Sorry, cannot have Promotions and" & vbCrLf & _
"Adjustments at the same time", 64, "Access into cell S3 not allowed."
End If
End If
End Sub

 

[AlphaFrog]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_SelectionChange([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    [color=darkblue]If[/color] Target.Count > 1 [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]If[/color] [color=darkblue]Not[/color] Intersect(Range("B4:E20"), Target) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        [color=darkblue]Select[/color] [color=darkblue]Case[/color] [color=darkblue]True[/color]
            [color=darkblue]Case[/color] Range("A" & Target.Row).Value = ""
                MsgBox "First select trainee attendance. ", vbExclamation, "Access Denied"
                Range("A" & Target.Row).Select
            [color=darkblue]Case[/color] L[color=darkblue]Case[/color](Left(Range("A" & Target.Row).Value, 1)) = "n"
                MsgBox "No attending trainee. ", vbExclamation, "Access Denied"
                Range("A" & Target.Row).Select
            Case Target.Column > 3 And Range("C" & Target.Row).Value = "Pass"
                MsgBox "Trainee passed exam. Re-test not allowed. ", vbExclamation, "Access Denied"
                Range("A" & Target.Row).Select
        [color=darkblue]End[/color] Select
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]

 

[Christiaan]

@AlphaFrog: In the test sheet, that code is working just fine.
But if I enter it in the original sheet, and update the range from

[COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Intersect(Range("B4:E20"), Target) [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then

[/COLOR] to

[COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Intersect(Range("X4:AB20"), Target) [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]

nothing happens...
Any ideas as of why?

 

[AlphaFrog]

@AlphaFrog: In the test sheet, that code is working just fine.
But if I enter it in the original sheet, and update the range from

[COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Intersect(Range("B4:E20"), Target) [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]

to

[COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Intersect(Range("X4:AB20"), Target) [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]

nothing happens...
Any ideas as of why?

The rest of the code refers to columns A to E as well.

 

[Christiaan]

Hmm. I feel silly. But yes, I updated the rest and now the messaging works fine.
But the cells are not being locked. How to go about that part?

Thank you for your help, I really appreciate it.