Блокировка и разблокировка ячеек на основе значения в другой ячейке для таблиц

Question

До сих пор в Интернете я видел способы, с помощью которых можно заблокировать ячейки в листе Excel на основе значения другой ячейки, однако мне еще предстоит найти что-либо, что относится к случаям, когда эти ячейки содержатся в таблице из-за динамизма. стол принеси.

Ниже у меня есть код VBA, адаптированный под мое собственное приложение, но он не работает.

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
    Unprotect Password:="Secret"

    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
                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
    Protect Password:="Secret"
End If 
End Sub

Возможные причины, почему это не работает;

1. Мои знания VBA все еще очень любительские, и поэтому я не смог их эффективно адаптировать
2. Метод пересечения, используемый в коде, может не работать с таблицами.

Что я пытаюсь достичь именно с помощью приведенного выше кода?

Я хотел бы убедиться, что столбцы таблицы; "Bus Discount Amount" и "Bus Discount Reason" заперта там нет автобуса скидки (т.е. если значение в столбце Bus discount "Да" , то укажите причину скидок (список) и введите сумму или иметь сумма автоматически генерируется в зависимости от выбора в поле "Причина скидки на автобус")

Я также использовал функцию Match рабочего листа, чтобы убедиться, что значение смещения является динамическим (т. Е. Если я вставлю новый столбец таблицы, свойство блокировки ячейки останется прежним).

Answer 1

Чтобы активировать блокирующие ячейки, вы должны использовать функцию защиты листа:

Private Sub Worksheet_Change(ByVal Target As Range)    Dim tbl As ListObject
  ActiveSheet.Unprotect
  Cells.Locked = false # all cells can be edited (because excel default is Cells.Locked = true)

  #here your code where you set cells to Locked = true 
  ...
  #end of the code

  ActiveSheet.Protect
End Sub