unprotect cells range

[Birnen]

Good afternoon.

sorry to bother again. but will you can help me. is that I am trying to format a group of cells, as follows.

if C17 contain a value, free from the E17 cell to AI17 in order to get access to write.

and this format would be equal from C17 to C167.

[JBeaucaire]

Let me be the first to say..."Huh?"

[Birnen]

[QUOTE=Birnen;2382735]Good afternoon.

sorry to bother again. but will you can help me. is that I am trying to format a group of cells, as follows.

if C17 contain a number, unprotect the range from, E17 cell to AI17, in order to get access to write.

and this format would be equal from C17 to C167.

[JBeaucaire]

1) Put this macro into the ThisWorkbook module, edit it to refer to the correct sheet and the correct password.

Private Sub workbook_Open()
    Sheets("Sheet1").Protect "password", UserInteraceOnly:=True
End Sub

Even though the sheet is protected, setting the UserInterfaceOnly flag when the workbook opens allows VBA to make changes to the sheet without protecting/unprotecting over and over.


2) Place this macro into the Sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

On Error Resume Next
    For Each cell In Target
        If Not Intersect(cell, Range("C17:C167")) Is Nothing Then
            If IsNumeric(cell) And cell <> 0 Then _
                Range("E" & cell.Row).Resize(1, 31).Locked = False
        End If
    Next cell

End Sub

As you work on the sheet, if you place a number into any cell in range C17:C167, that row will be unlocked. Happens in realtime.

[Birnen]

1) Put this macro into the ThisWorkbook module, edit it to refer to the correct sheet and the correct password.

Private Sub workbook_Open()
    Sheets("Sheet1").Protect "password", UserInteraceOnly:=True
End Sub

Even though the sheet is protected, setting the UserInterfaceOnly flag when the workbook opens allows VBA to make changes to the sheet without protecting/unprotecting over and over.


2) Place this macro into the Sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

    For Each cell In Target
        If Not Intersect(cell, Range("C17:C167")) Is Nothing Then
            If IsNumeric(cell) And cell <> 0 Then _
                Range("E" & cell.Row).Resize(1, 31).Locked = False
        End If
    Next cell

End Sub

As you work on the sheet, if you place a number into any cell in range C17:C167, that row will be unlocked. Happens in realtime.

give me error in IsNumeric...

[JBeaucaire]

give me error in IsNumeric...

I've edited macro #2 above to try and resolve that.

[ChemistB]

This is less secure as people can copy and paste items in but if they are manually entering data, you can use Data Validation to protect those cells.

Select E17:AI167, then go to Data Validation>Custom
Uncheck "Ignore blanks"
enter =LEN($C17)>0 ENTER
Type in an appropriate Error Alert "Column C must be filled in first."
Hit OK