Locking and unlocking cells based on value in another cell

[eios]

Afternoon all,

This is what I want to happen but I have no idea how to do it.

If C14 = "IV" THEN E14 = Locked AND F14 = Unlocked
If C14 = "RV" THEN E14 = Unlocked AND F14 = Locked
If C14 = "AJ" THEN E14 AND F14 = Unlocked

Another thing I would like to make possible is for this to work throughout cells C14:C450, E14:E450 and F14:F450

Can anyone help me out on this?

 

[ray_b]

Anyone with an idea of what the code would look like?

Hi All,

The information and help we find on this forum is amazing. Although Excel is a passion of mine, I am no expect when it comes to VBA and macro codes...

I also have the same problem. I was wondering if someone could help me with the code that would execute the following throughout the cell range F7:F25, I7:I25 and L7:L25.

If F7 = AND(F7>0, F7<1000) THEN I7 = Unlocked AND L7 = Locked
If F7 >= 1000 THEN I7 = Locked AND L7 = Unlocked
If F7 <= 0 THEN I7 AND L7 = Locked

Same for line 8 to 25.

Merry Christmas and best wishes for this holiday season to everyone.

Regards

 

[AlphaFrog]

See post #2 for instructions.
Change the password to suit.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    [color=darkblue]Dim[/color] cell [color=darkblue]As[/color] Range
    [color=darkblue]If[/color] [color=darkblue]Not[/color] Intersect(Range("F7:F25"), Target) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        Me.Unprotect Password:="Secret"
        [color=darkblue]For[/color] [color=darkblue]Each[/color] cell [color=darkblue]In[/color] Intersect(Range("F7:F25"), Target)
            [color=darkblue]Select[/color] [color=darkblue]Case[/color] [color=darkblue]True[/color]
                [color=darkblue]Case[/color] cell > 0 And cell < 1000       [color=green]'If F7 = AND(F7>0, F7<1000) THEN I7 = Unlocked AND L7 = Locked[/color]
                    cell.Offset(, 3).Locked = [color=darkblue]False[/color]
                    cell.Offset(, 6).Locked = [color=darkblue]True[/color]
                [color=darkblue]Case[/color] cell >= 1000                   [color=green]'If F7 >= 1000 Then I7 = Locked And L7 = Unlocked[/color]
                    cell.Offset(, 3).Locked = [color=darkblue]True[/color]
                    cell.Offset(, 6).Locked = [color=darkblue]False[/color]
                [color=darkblue]Case[/color] cell <= 0                      [color=green]'If F7 <= 0 THEN I7 AND L7 = Locked[/color]
                    cell.Offset(, 3).Locked = [color=darkblue]True[/color]
                    cell.Offset(, 6).Locked = [color=darkblue]True[/color]
            [color=darkblue]End[/color] [color=darkblue]Select[/color]
        [color=darkblue]Next[/color] cell
        Me.Protect Password:="Secret"
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]

 

[ray_b]

Thank you Alpha! It works just perfectly!!

See post #2 for instructions.
Change the password to suit.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]Dim[/COLOR] cell [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Intersect(Range("F7:F25"), Target) [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        Me.Unprotect Password:="Secret"
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] cell [COLOR=darkblue]In[/COLOR] Intersect(Range("F7:F25"), Target)
            [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]True[/COLOR]
                [COLOR=darkblue]Case[/COLOR] cell > 0 And cell < 1000       [COLOR=green]'If F7 = AND(F7>0, F7<1000) THEN I7 = Unlocked AND L7 = Locked[/COLOR]
                    cell.Offset(, 3).Locked = [COLOR=darkblue]False[/COLOR]
                    cell.Offset(, 6).Locked = [COLOR=darkblue]True[/COLOR]
                [COLOR=darkblue]Case[/COLOR] cell >= 1000                   [COLOR=green]'If F7 >= 1000 Then I7 = Locked And L7 = Unlocked[/COLOR]
                    cell.Offset(, 3).Locked = [COLOR=darkblue]True[/COLOR]
                    cell.Offset(, 6).Locked = [COLOR=darkblue]False[/COLOR]
                [COLOR=darkblue]Case[/COLOR] cell <= 0                      [COLOR=green]'If F7 <= 0 THEN I7 AND L7 = Locked[/COLOR]
                    cell.Offset(, 3).Locked = [COLOR=darkblue]True[/COLOR]
                    cell.Offset(, 6).Locked = [COLOR=darkblue]True[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
        [COLOR=darkblue]Next[/COLOR] cell
        Me.Protect Password:="Secret"
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

 

[ray_b]

Thank you Alpha! It works just perfectly!!

Well it seems like the code may bring a little problem into my spreadsheet. I need to filter and sort. Before locking the spreadsheet with my password I select the filter and sort options. Somehow they keep getting disabled when the spreadsheet is locked (only the "Select unlocked cells" option remains enable at all time) so I don't fully get what I need.

Is there a possibility that the code is causing this?

Thanks for the help.

Regards

 

[AlphaFrog]

Well it seems like the code may bring a little problem into my spreadsheet. I need to filter and sort. Before locking the spreadsheet with my password I select the filter and sort options. Somehow they keep getting disabled when the spreadsheet is locked (only the "Select unlocked cells" option remains enable at all time) so I don't fully get what I need.

Is there a possibility that the code is causing this?

Thanks for the help.

Regards

I don't follow the order of events. If you unprotect before Filter\Sort, what does it matter what the protected settings are?

I don't know if this will help, but it will not lock filter\sort when the sheet is protected

Me.Protect Password:="Secret", AllowSorting:=True, AllowFiltering:=True

 

[ray_b]

I don't follow the order of events. If you unprotect before Filter\Sort, what does it matter what the protected settings are?

I don't know if this will help, but it will not lock filter\sort when the sheet is protected

Me.Protect Password:="Secret", AllowSorting:=True, AllowFiltering:=True

It works. This minor change has fixed my problem. Now even when I unprotect the spreadsheet the "filter" and "sort" options remain selected and don't change after the locking is turned on (the protection options remain the same to what I need - it wasn't the case before).

I don't want to have to unprotect the spreadsheet to filter/sort as different users will be using the spreadsheet and I don't want them to mess it up.

Alpha, thanks again for your help and very quick reply.

Regards

 

[MrMarho]

Re: Locking and unlocking cells based on value in another cell for tables

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("C14:C450")) Is Nothing Then
        Dim cell As Range
        [COLOR=Blue]Unprotect Password:="Secret"[/COLOR]
        For Each cell In Intersect(Target, Range("C14:C450"))
            Select Case cell.Value
                Case "IV"
                    cell.Offset(0, 2).Locked = True     'Column E
                    cell.Offset(0, 3).Locked = False    'Column F
                Case "RV"
                    cell.Offset(0, 2).Locked = False    'Column E
                    cell.Offset(0, 3).Locked = True     'Column F
                Case "AJ"
                    cell.Offset(0, 2).Locked = False    'Column E
                    cell.Offset(0, 3).Locked = False    'Column F
                Case Else
                    cell.Offset(0, 2).Locked = True     'Column E
                    cell.Offset(0, 3).Locked = True     'Column F
            End Select
        Next cell
        [COLOR=Blue]Protect Password:="Secret"[/COLOR]
    End If
    
End Sub

Hi guys, I hope this isn't two years late?

Well, so I am trying to adapt the code above to suit a table created in the worksheet as compared to just a range.

I have the code below:

Private Sub Worksheet_Change(ByVal Target As Range)    Dim tbl As ListObject
    Set tbl = Worksheets("Nursery").ListObjects("TableName")


    If Not Intersect(Target, tbl.ListColumns("Bus Discount").Range) Is Nothing Then
        Dim CellBusDiscount As Range
        [COLOR=Blue]Unprotect Password:="Secret"
[/COLOR]
        For Each CellBusDiscount In Intersect(Target, tbl.ListColumns("Bus Discount").Range)
            Select Case CellBusDiscount.Value
                Case "Yes"
                    CellBusDiscount.Offset(0, Application.Worksheet.Function.Match("Bus Reason", tbl.HeaderRowRange, 0) - Application.WorksheetFunction.Match("Bus Discount", tbl.HeaderRowRange, 0)).Locked = False     'Column E
                    CellBusDiscount.Offset(0, Application.Worksheet.Function.Match("Bus Discount Amt", tbl.HeaderRowRange, 0) - Application.WorksheetFunction.Match("Bus Discount", tbl.HeaderRowRange, 0)).Locked = False
                Case "No"
                    CellBusDiscount.Offset(0, Application.Worksheet.Function.Match("Bus Reason", tbl.HeaderRowRange, 0) - Application.WorksheetFunction.Match("Bus Discount", tbl.HeaderRowRange, 0)).Locked = True     
                    CellBusDiscount.Offset(0, Application.Worksheet.Function.Match("Bus Discount Amt", tbl.HeaderRowRange, 0) - Application.WorksheetFunction.Match("Bus Discount", tbl.HeaderRowRange, 0)).Locked = True
                Case Else
                    CellBusDiscount.Offset(0, Application.Worksheet.Function.Match("Bus Reason", tbl.HeaderRowRange, 0) - Application.WorksheetFunction.Match("Bus Discount", tbl.HeaderRowRange, 0)).Locked = True     
                    CellBusDiscount.Offset(0, Application.Worksheet.Function.Match("Bus Discount Amt", tbl.HeaderRowRange, 0) - Application.WorksheetFunction.Match("Bus Discount", tbl.HeaderRowRange, 0)).Locked = True
            End Select
        Next cell
        [COLOR=Blue]Protect Password:="Secret"[/COLOR]
    End If
    
End Sub

What am I trying to achieve above with this code?

I would like to make sure the table columns; "Bus Discount Amount" and "Bus Discount Reason" are locked is there is no Bus discount (i.e. If Bus discount is "Yes" then state the reason for the discount (dropdown list) and enter the amount or have the amount automatically generated depending on the selection in the "Bus Discount Reason")

I have also used the Worksheet Function Match to make sure the offset value is dynamic (i.e. if i insert a new table column the cell lock property will remain the same).

    If Not Intersect(Target, tbl.ListColumns("Bus Discount").Range) Is Nothing Then
        Dim CellBusDiscount As Range
        [COLOR=Blue]Unprotect Password:="Secret"
[/COLOR]
        For Each CellBusDiscount In Intersect(Target, tbl.ListColumns("Bus Discount").Range)

I think I maybe having the problem here I have tried to look up documentation in the Intersect method and I am not sure whether it doesn't quite work because it is a table.

Any help will be greatly appreciated.

I look forward to your life-saving answers