Unlock Cells if Another Cell is populated

[rkormoski]

There has been posts already on this topic, but mine is a little bit different than the others I came across and none of which have helped me.

I have a workbook, which basically is acting as a form. In cell B2 of the sheet, "form" the user needs to enter a date. Once the user enters a date, the cells B4:B15 become unlocked so that the user may enter a value for each of those cells, which correspond to a # of units sold on that day. Any help would be appreciated.

 

[Progrower]

Set up "data validation" for B2 to be a date. Then put this code in the Sheets code module.
I assumed your using "sheet1".

Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("Sheet1")
    If .Range("B2") <> "" Then
       .Unprotect
       .Range("B4:B15").Locked = False
       .Protect
    Else
       .Unprotect
       .Range("B4:B15").Locked = True
       .Protect
    End If

End With
End Sub

 

[rkormoski]

It's not working properly. Should the form already be protected and if so, what cells should be protected ?

I currently have it setup so that only B1 is unprotected right now. I was hoping that the code would unprotect cells B4:B15 once a date has been put into cell B1. But even after I enter a date into cell B1, when I try and type into cell B4, I get a READ ONLY error message.

Thanks !

 

[Progrower]

You said
"In cell B2 of the sheet, "form" the user needs to enter a date."

format all cells unlocked and change my B2 refrance in code to B1 if thats the cell with the date.
hope this helps

 

[rkormoski]

Thanks. Sorry about the incorrect cell reference. Was a long day.

 

[rkormoski]

This works great, thanks ! One last question: If I want to unlock a 2nd range of cells, how would I do that ? For example, I want to unlock (B4:B15) and (B20:B35) once a date is entered into B2. The modifcations I am trying to make to the code you provided me is not working and only unlocks the first set of cells.

Thanks again

 

[Progrower]

Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("Sheet1")
    If .Range("B2") <> "" Then
       .Unprotect
       .Range("B4:B15,B20:B35").Locked = False
       .Protect
    Else
       .Unprotect
       .Range("B4:B15,B20:B35").Locked = True
       .Protect
    End If

End With
End Sub