Previous Topic Next Topic

Lock cell in Excel worksheet with VB

Solved/Closed
MartynP Posts 5 Registration date Thursday 13 November 2014 Status Member Last seen 17 March 2015 - 13 Nov 2014 à 09:34
 Martynp - 19 Nov 2014 à 08:38
I have an worksheet that I need to lock the cell based on the value of another cell. I have managed to create VB which will do this for one row but need this to work for up to 1000 rows.

I use columns A to Q and rows 7 to 1000, when "Y" is entered in Q7 the cells A7 to Q7 lock

the VB I have created is below (which will lock row 7)


Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.Unprotect

If ActiveSheet.Cells(7, 13).Text = "Y" Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range(Cells(7, 1), Cells(7, 13)).Locked = True
Else
ActiveSheet.Range(Cells(7, 1), Cells(7, 13)).Locked = False
End If
If ActiveSheet.Cells(8, 13).Text = "Y" Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range(Cells(8, 1), Cells(8, 13)).Locked = True
Else
ActiveSheet.Range(Cells(8, 1), Cells(8, 13)).Locked = False
End If
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

I know I will not be able to add each row as it will be to large, so is there a more efficient way to do this.

Thanks

2 responses

Réponse 1 / 2
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
15 Nov 2014 à 06:47
I have not really tested this one. But i think it should work for u 


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rowId As Long

    If (Intersect(Target, Range(Cells(1, "H"), Cells(1000, "H"))) Is Nothing) Then
        Exit Sub
    End If
    
    ActiveSheet.Unprotect ("")
    For Each Row In Target.Rows
        rowId = Row.Row
        Range(Cells(rowId, "A"), Cells(rowId, "H")).Locked = (Cells(rowId, "H") = "Y")
    Next
    
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub
MartynP
17 Nov 2014 à 06:29
Thanks rizvisa1, did test this and unable to get this to work, didn't seem to lock the cells.

Thanks
Réponse 2 / 2
MartynP Posts 5 Registration date Thursday 13 November 2014 Status Member Last seen 17 March 2015
17 Nov 2014 à 08:19
Hi rizvisa1
I think I have sorted this, just going to do some testing.

Many Thanks
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
19 Nov 2014 à 06:54
Works for me. Only thing that i can think of is that you may have "y" instead of "Y". in that case you can change the line

(Cells(rowId, "H") = "Y")
to

(UCase(Cells(rowId, "H")) = "Y")
Martynp
19 Nov 2014 à 08:38
Thanks rizisa
I did get it to work, and as you said it was the "y"

Many thanks

Similar discussions

If a cell contains any text then add a value
cbrizz -
Pharmanumbers -

20 responses
How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
Clear cell contents based on value of another cell range using VBA
Tbonekiller -
Tbonekiller -

6 responses