Lock Sheet Row Based on Value in Column 8

[tomexcel1]

Hi All

I currently have the below code as a worksheet change, however i now need it to run when a button is pressed. Can someone please help me convert it to work like that, ive tired but i keep getting an object error.

[COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR]    Sheet17.Unprotect ("password")
    Dim cl As Range
    If Target.Column = 8 Then
        For Each cl In Target.Cells
            If UCase(cl.Value) = UCase("Authorised") And cl.Column = 8 Then
                Range("a" & cl.Row & ":h" & cl.Row).Locked = True
            Else
                Range("a" & cl.Row & ":h" & cl.Row).Locked = False
            End If
        Next
    End If
    Sheet17.Protect ("password")[COLOR=#333333]End Sub[/COLOR]

Thanks in advance

 

[gallen]

Hello,

You just need to add the code to it's own Sub then call the sub from the click event of the button (slightly different dependant on Form control/active x control)

For example:

Private Sub LockRow()
    
    'handle any errors
    On Error GoTo errHandle
    
    'ensure only one cell is active
    If Selection.Cells.Count <> 1 Then Exit Sub
    
    Sheet17.Unprotect ("password")
    Dim cl As Range
    If ActiveCell.Column = 8 Then
        For Each cl In ActiveCell.Cells
            If UCase(cl.Value) = UCase("Authorised") And cl.Column = 8 Then
                Range("a" & cl.row & ":h" & cl.row).Locked = True
            Else
                Range("a" & cl.row & ":h" & cl.row).Locked = False
            End If
        Next
    End If
    Sheet17.Protect ("password")
Exit Sub
'handle any errors
errHandle:
    MsgBox Err.Description, vbCritical, "Err No: " & Err.Number
End Sub




Private Sub CommandButton1_Click()
    LockRow
End Sub