Help With VBA Locking Table data Column Based on Cell Value

[JonnieO]

Greetings Excel gurus. I have run into an issue that I cant seem to resolve and I know the answer is probably simple. I am using Excel 2010

I have a spreadsheet that was built to allow users to enter data, there are two columns in the table that need to either be locked or unlocked based on what Billing code is being worked on for that sheet.

For example, use billing codes "BC1, BC2, BC3, BC4, BC5, and BC6". If a user selects BC1, from the drop down in cell "A2", as the code for that worksheet (they may enter anywhere from 2 or 3 to 300 entries), then the "Count/Unit" data column (column I) should be LOCKED and set the value to a default of 1 (as long as there is a value in the corresponding cell in columns A and G, while at the same time all the data cells in the "value" column (column H) should be unlocked and allow the user to enter a financial amount, such as 12345, which the cell now converts to 123.45.

Any of the other billing code options (BC2-BC6) should result in the "Value" column being LOCKED and the "Count/Unit" data column being UNLOCKED.

Each sheet is for one billing code, so there will not be different codes per line, at least not yet.

The code I have been playing with, without setting the default value to 1 is as follows

Within the Worksheet portion

Private Sub Worksheet_Change(ByVal Target As Range)

ThisWorkbook.Cell_Lock_Status

End Sub

Within the Workbook portion

Sub Cell_Lock_Status()    If Sheets("Billing_Entries").Range("A2") = Sheets("Import Billing Codes").Range("B3") Then
        Sheets("Billing_Entries").Range("Table3[[Count/Unit]]").Locked = True
    Else
        Sheets("Billing_Entries").Range("Table3[[Count/Unit]]").Locked = False
    End If
End Sub

Any help would be greatly appreciated.

 

[JonnieO]

So I have gotten the code to lock and unlock the "Count/Unit" data column cells based on the value of cell "A2" using the code below:

Sub Cell_Lock_Status()
    Sheets("Billing_Entries").Unprotect Password:=""
    If Sheets("Billing_Entries").Range("A2") = Sheets("Import Billing Codes").Range("B3") Then
        Sheets("Billing_Entries").Range("Table3[[Count/Unit]]").Locked = True


    Else
        Sheets("Billing_Entries").Range("Table3[[Count/Unit]]").Locked = False
    
   End If
      Sheets("Billing_Entries").Protect Password:="", AllowFiltering:=True, Contents:=True, AllowInsertingRows:=True, AllowDeletingRows:=True, AllowSorting:=True
End Sub

However, I am trying to add multiple actions to the code and I am getting the "runtime error 1004 application defined or object defined" error.

The additional actions include

1. Keeping the "Value" column UNLOCKED and the "Count/Unit" column LOCKED if cell A2 is BC1
2. Adding a formula to every cell in the "Count/Unit" data column that has a value in the corresponding cell in columns "A" and "H", that formula is IF(AND(F5<>"",H5>0),1,"")

The formula is as follows

Sub Cell_Lock_Status()
    Sheets("Billing_Entries").Unprotect Password:=""
    If Sheets("Billing_Entries").Range("A2") = Sheets("Import Billing Codes").Range("B3") Then
[COLOR=#800000]        Worksheets("Billing_Entries").Range("I4").Formula = "=IF(AND(F5<>"",H5>0),1,"")"[/COLOR]
[COLOR=#800000]        Worksheets("Billing_Entries").Range("I4:" & "I" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown[/COLOR]
        Sheets("Billing_Entries").Range("Table3[[Count/Unit]]").Locked = True
        Sheets("Billing_Entries").Range("Table3[[Value]]").Locked = False


    Else
        Sheets("Billing_Entries").Range("Table3[[Count/Unit]]").Locked = False
[COLOR=#800000]        Sheets("Billing_Entries").Range("Table3[[Value]]").Locked = True[/COLOR]


    End If
      Sheets("Billing_Entries").Protect Password:="", AllowFiltering:=True, Contents:=True, AllowInsertingRows:=True, AllowDeletingRows:=True, AllowSorting:=True
End Sub