VBA issue with adding formulas and locking cells

M

MissingInAction

Board Regular
Joined
Sep 20, 2019
Messages
85
Office Version
  1. 365
Platform
  1. Windows
Hi everyone

I have some VBA code that runs automatically based on when a particular cell is selected. I tried writing the code in such a way, that if the user swaps between the different options, every option will work, no matter the order in which you select them (in the event of a miss click).
Here is the code:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$7" Then
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect "abc123"
        If ActiveSheet.Range("B7").Value = "New Account" Then
            Range("C14:E14").Select
'           Performance Contract dropdown
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=Data!$G$2:$G$3"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = "Select ""Yes"" if the employee does not need any form of IT/phone/alarm code access, otherwise select ""No""."
                .ErrorMessage = "Please select Yes or No"
                .ShowInput = True
                .ShowError = True
            End With
            Range("D7:E7,B15,B26,D9").Select
            Range("B26").Activate
            Selection.Locked = True
            Selection.FormulaHidden = False
            Range("B9:B12,D10:D11").Select
            Range("D11").Activate
            Selection.Locked = False
            Selection.FormulaHidden = False
            Selection.ClearContents
            Range("B8").Select
        Else
            If ActiveSheet.Range("B7").Value = "Termination of Account" Then
                ActiveSheet.Unprotect "abc123"
                Application.ScreenUpdating = False
                Range("B9:B12,D7:D8,D9:E11,C14:E14").Select
                Range("C14:E14").Activate
                Selection.Locked = False
                Selection.FormulaHidden = False
                Range("B9").Select
                ActiveCell.FormulaR1C1 = _
                    "=IF(R8C2="""","""",IF(R7C2=""Termination of Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C7,""Unknown Employee"")))"
                Range("B10").Select
                ActiveCell.FormulaR1C1 = _
                    "=IF(R8C2="""","""",IF(R7C2=""Termination of Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C2,""Unknown Employee"")))"
                Range("B11").Select
                ActiveCell.FormulaR1C1 = _
                    "=IF(R8C2="""","""",IF(R7C2=""Termination of Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C8,""Unknown Employee"")))"
                Range("B12:B13").Select
                ActiveCell.FormulaR1C1 = _
                    "=IF(R8C2="""","""",IF(R7C2=""Termination of Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C9,""Unknown Employee"")))"
                Range("D9").Select
                ActiveCell.Formula2R1C1 = _
                    "=IF(R8C2="""","""",IF(R9C3<>"""",IF(XLOOKUP(R8C2,'User Data'!C6,'User Data'!C11,"""")=0,"""",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C11,"""")),""""))"
                Range("D10:E10").Select
                ActiveCell.FormulaR1C1 = _
                    "=IF(R8C2="""","""",IF(R7C2=""Termination of Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C14,""Unknown Employee"")))"
                Range("D11:E11").Select
                ActiveCell.FormulaR1C1 = _
                    "=IF(R8C2="""","""",IF(R7C2=""Termination of Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C12,""Unknown Employee"")))"
                Range("B9:B13,D10:E11").Select
                Range("D10:E11").Activate
                Selection.Locked = True
                Selection.FormulaHidden = False
                Range("B8").Select
            Else
                If ActiveSheet.Range("B7").Value = "Change Account" Then
                    ActiveSheet.Unprotect "abc123"
                    Application.ScreenUpdating = False
                    Range("B9:B12,D9:D11,C14:E14").Select
                    Range("C14").Activate
                    Selection.Locked = False
                    Selection.FormulaHidden = False
                    Selection.ClearContents
                    Range("B9").Select
                    ActiveCell.FormulaR1C1 = _
                        "=IF(R8C2="""","""",IF(R7C2=""Change Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C7,""Unknown Employee"")))"
                    Range("B10").Select
                    ActiveCell.FormulaR1C1 = _
                        "=IF(R8C2="""","""",IF(R7C2=""Change Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C2,""Unknown Employee"")))"
                    Range("B11").Select
                    ActiveCell.FormulaR1C1 = _
                        "=IF(R8C2="""","""",IF(R7C2=""Change Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C8,""Unknown Employee"")))"
                    Range("B9:B11").Select
                    Range("B9:B11").Activate
                    Selection.Locked = True
                    Selection.FormulaHidden = False
                    Range("B8").Select
                Else
                    Range("B9:B12,D9:E11").Select
                    Range("D10").Activate
                    Selection.Locked = False
                    Selection.FormulaHidden = False
                    Selection.ClearContents
                    Range("B8").Select
                End If
            End If
            Range("C14:E14").Select
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
                :=xlBetween
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
            Range("B8").Select
        End If
    ActiveSheet.Protect "abc123"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Application.ScreenUpdating = True
    End If
    
    If Target.Address = "$B$9" Then
       Application.ScreenUpdating = False
       ActiveSheet.Unprotect "abc123"
        If ActiveSheet.Range("B9").Value = "Contract" Or ActiveSheet.Range("B9").Value = "Councillor" Then
            Range("D9:E9").Select
            Range("D9").Activate
            Selection.Locked = False
            Selection.FormulaHidden = False
            Selection.ClearContents
            Range("B10").Select
        Else
            Range("D9:E9").Select
            Range("D9").Activate
            Selection.Locked = True
            Selection.FormulaHidden = False
            Range("B10").Select
        End If
    ActiveSheet.Protect "abc123"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Application.ScreenUpdating = True
    End If
End Sub

The problem is that when I select "Termination of Account", I get an 'Run-time error 1004': Application-defined or object-defined error". These lines then get highlighted:
VBA Code: 
                ActiveCell.Formula2R1C1 = _
                    "=IF(R8C2="""","""",IF(R9C3<>"""",IF(XLOOKUP(R8C2,'User Data'!C6,'User Data'!C11,"""")=0,"""",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C11,"""")),""""))"
I have narrowed it down to cell B11 not being unlocked, but I do have above the formula that lists ranges that must be unlocked.
If I comment out the highlighted code above, then I get an error stating "Unable to set the Locked property of the Range class". Again, probably because the cell was not unlocked in the first place. The exact same error occurs when I select "Change Account" instead of "Termination of Account".

The second issue is that when I work on a second workbook (completely unrelated to this one), I get the error "Run-time error '9': Subscript out of range" and then this portion of code gets highlighted:
VBA Code: 
If Worksheets("User Form").Range("E50").Value = "Yes" Or Worksheets("User Form").Range("E50").Value = "yes" Then

Here is the full code:
VBA Code: 
Private Sub Worksheet_Calculate()
    
'   IT Equipment
    If Worksheets("User Form").Range("E50").Value = "Yes" Or Worksheets("User Form").Range("E50").Value = "yes" Then
            Worksheets("User Form").Shapes("Laptop").Visible = True
            Worksheets("User Form").Shapes("Desktop").Visible = True
            Worksheets("User Form").Shapes("Other").Visible = True
        Else
            Worksheets("User Form").Shapes("Laptop").Visible = False
            Worksheets("User Form").Shapes("Desktop").Visible = False
            Worksheets("User Form").Shapes("Other").Visible = False
    End If
    
'   Alarm Code & Building Keys
    If Worksheets("User Form").Range("B55").Value = "Yes" Or Worksheets("User Form").Range("B55").Value = "yes" Then
            Worksheets("User Form").Shapes("Barrydale").Visible = True
            Worksheets("User Form").Shapes("BredasdorpAnnex").Visible = True
            Worksheets("User Form").Shapes("BredasdorpFire").Visible = True
            Worksheets("User Form").Shapes("BredasdorpHeadOffice").Visible = True
            Worksheets("User Form").Shapes("BredasdorpRoads").Visible = True
            Worksheets("User Form").Shapes("BredasdorpSCM").Visible = True
            Worksheets("User Form").Shapes("BredasdorpWorkshop").Visible = True
            Worksheets("User Form").Shapes("CaledonFire").Visible = True
            Worksheets("User Form").Shapes("CaledonHealth").Visible = True
            Worksheets("User Form").Shapes("CaledonRoads").Visible = True
            Worksheets("User Form").Shapes("DieDam").Visible = True
            Worksheets("User Form").Shapes("GrabouwFire").Visible = True
            Worksheets("User Form").Shapes("GrabouwHealth").Visible = True
            Worksheets("User Form").Shapes("Hermanus").Visible = True
            Worksheets("User Form").Shapes("Karwyderskraal").Visible = True
            Worksheets("User Form").Shapes("Kleinmond").Visible = True
            Worksheets("User Form").Shapes("Struisbaai").Visible = True
            Worksheets("User Form").Shapes("SwellendamFire").Visible = True
            Worksheets("User Form").Shapes("SwellendamHealth").Visible = True
            Worksheets("User Form").Shapes("SwellendamRoads").Visible = True
            Worksheets("User Form").Shapes("Uilenkraalsmond").Visible = True
            Worksheets("User Form").Shapes("Villiersdorp").Visible = True
        Else
            Worksheets("User Form").Shapes("Barrydale").Visible = False
            Worksheets("User Form").Shapes("BredasdorpAnnex").Visible = False
            Worksheets("User Form").Shapes("BredasdorpFire").Visible = False
            Worksheets("User Form").Shapes("BredasdorpHeadOffice").Visible = False
            Worksheets("User Form").Shapes("BredasdorpRoads").Visible = False
            Worksheets("User Form").Shapes("BredasdorpSCM").Visible = False
            Worksheets("User Form").Shapes("BredasdorpWorkshop").Visible = False
            Worksheets("User Form").Shapes("CaledonFire").Visible = False
            Worksheets("User Form").Shapes("CaledonHealth").Visible = False
            Worksheets("User Form").Shapes("CaledonRoads").Visible = False
            Worksheets("User Form").Shapes("DieDam").Visible = False
            Worksheets("User Form").Shapes("GrabouwFire").Visible = False
            Worksheets("User Form").Shapes("GrabouwHealth").Visible = False
            Worksheets("User Form").Shapes("Hermanus").Visible = False
            Worksheets("User Form").Shapes("Karwyderskraal").Visible = False
            Worksheets("User Form").Shapes("Kleinmond").Visible = False
            Worksheets("User Form").Shapes("Struisbaai").Visible = False
            Worksheets("User Form").Shapes("SwellendamFire").Visible = False
            Worksheets("User Form").Shapes("SwellendamHealth").Visible = False
            Worksheets("User Form").Shapes("SwellendamRoads").Visible = False
            Worksheets("User Form").Shapes("Uilenkraalsmond").Visible = False
            Worksheets("User Form").Shapes("Villiersdorp").Visible = False
    End If

'   Eunomia
    If Worksheets("User Form").Range("D136").Value = "Yes" Or Worksheets("User Form").Range("D136").Value = "yes" Then
            Worksheets("User Form").Shapes("Eunomia_Action_Owner").Visible = True
            Worksheets("User Form").Shapes("Eunomia_Approver").Visible = True
            Worksheets("User Form").Shapes("Eunomia_Administrator").Visible = True
            Worksheets("User Form").Shapes("Eunomia_Assist_Administrator").Visible = True
        Else
            Worksheets("User Form").Shapes("Eunomia_Action_Owner").Visible = False
            Worksheets("User Form").Shapes("Eunomia_Approver").Visible = False
            Worksheets("User Form").Shapes("Eunomia_Administrator").Visible = False
            Worksheets("User Form").Shapes("Eunomia_Assist_Administrator").Visible = False
    End If

'   Risk Management
    If Worksheets("User Form").Range("B136").Value = "Yes" Or Worksheets("User Form").Range("B136").Value = "yes" Then
            Worksheets("User Form").Shapes("Risk_Administrator").Visible = True
            Worksheets("User Form").Shapes("Risk_Assist_Administrator").Visible = True
            Worksheets("User Form").Shapes("Risk_Risk_Owner").Visible = True
            Worksheets("User Form").Shapes("Risk_Action_Owner").Visible = True
        Else
            Worksheets("User Form").Shapes("Risk_Administrator").Visible = False
            Worksheets("User Form").Shapes("Risk_Assist_Administrator").Visible = False
            Worksheets("User Form").Shapes("Risk_Risk_Owner").Visible = False
            Worksheets("User Form").Shapes("Risk_Action_Owner").Visible = False
    End If

'   Risk Management - Action Owner
    If Worksheets("User Form").Range("G140").Value = True Then
            Worksheets("User Form").Shapes("Risk_Auditing").Visible = True
            Worksheets("User Form").Shapes("Risk_CFO").Visible = True
            Worksheets("User Form").Shapes("Risk_Committee").Visible = True
            Worksheets("User Form").Shapes("Risk_Community_Director").Visible = True
            Worksheets("User Form").Shapes("Risk_Councillors").Visible = True
            Worksheets("User Form").Shapes("Risk_Emergency").Visible = True
            Worksheets("User Form").Shapes("Risk_Environment").Visible = True
            Worksheets("User Form").Shapes("Risk_Expenditure").Visible = True
            Worksheets("User Form").Shapes("Risk_BTO").Visible = True
            Worksheets("User Form").Shapes("Risk_Financial").Visible = True
            Worksheets("User Form").Shapes("Risk_HR").Visible = True
            Worksheets("User Form").Shapes("Risk_IDP").Visible = True
            Worksheets("User Form").Shapes("Risk_ICT").Visible = True
            Worksheets("User Form").Shapes("Risk_LED").Visible = True
            Worksheets("User Form").Shapes("Risk_Mun_Health").Visible = True
            Worksheets("User Form").Shapes("Risk_MM").Visible = True
            Worksheets("User Form").Shapes("Risk_Performance").Visible = True
            Worksheets("User Form").Shapes("Risk_Revenue").Visible = True
            Worksheets("User Form").Shapes("Risk_Risk").Visible = True
            Worksheets("User Form").Shapes("Risk_Roads").Visible = True
            Worksheets("User Form").Shapes("Risk_SCM").Visible = True
        Else
            Worksheets("User Form").Shapes("Risk_Auditing").Visible = False
            Worksheets("User Form").Shapes("Risk_CFO").Visible = False
            Worksheets("User Form").Shapes("Risk_Committee").Visible = False
            Worksheets("User Form").Shapes("Risk_Community_Director").Visible = False
            Worksheets("User Form").Shapes("Risk_Councillors").Visible = False
            Worksheets("User Form").Shapes("Risk_Emergency").Visible = False
            Worksheets("User Form").Shapes("Risk_Environment").Visible = False
            Worksheets("User Form").Shapes("Risk_Expenditure").Visible = False
            Worksheets("User Form").Shapes("Risk_BTO").Visible = False
            Worksheets("User Form").Shapes("Risk_Financial").Visible = False
            Worksheets("User Form").Shapes("Risk_HR").Visible = False
            Worksheets("User Form").Shapes("Risk_IDP").Visible = False
            Worksheets("User Form").Shapes("Risk_ICT").Visible = False
            Worksheets("User Form").Shapes("Risk_LED").Visible = False
            Worksheets("User Form").Shapes("Risk_Mun_Health").Visible = False
            Worksheets("User Form").Shapes("Risk_MM").Visible = False
            Worksheets("User Form").Shapes("Risk_Performance").Visible = False
            Worksheets("User Form").Shapes("Risk_Revenue").Visible = False
            Worksheets("User Form").Shapes("Risk_Risk").Visible = False
            Worksheets("User Form").Shapes("Risk_Roads").Visible = False
            Worksheets("User Form").Shapes("Risk_SCM").Visible = False
    End If

'   SDBIP
    If Worksheets("User Form").Range("B149").Value = "Yes" Or ActiveSheet.Range("B149").Value = "yes" Then
            ActiveSheet.Shapes("SDBIP_Administrator").Visible = True
            ActiveSheet.Shapes("SDBIP_Assist_Administrator").Visible = True
            ActiveSheet.Shapes("SDBIP_HOD").Visible = True
            Worksheets("User Form").Shapes("SDBIP_KPI_Owner").Visible = True
        Else
            Worksheets("User Form").Shapes("SDBIP_Administrator").Visible = False
            Worksheets("User Form").Shapes("SDBIP_Assist_Administrator").Visible = False
            Worksheets("User Form").Shapes("SDBIP_HOD").Visible = False
            Worksheets("User Form").Shapes("SDBIP_KPI_Owner").Visible = False
    End If

'   SDBIP - KPI Owner
    If Worksheets("User Form").Range("G153").Value = True Then
            Worksheets("User Form").Shapes("SDBIP_Auditing").Visible = True
            Worksheets("User Form").Shapes("SDBIP_CFO").Visible = True
            Worksheets("User Form").Shapes("SDBIP_Committee").Visible = True
            Worksheets("User Form").Shapes("SDBIP_Community_Director").Visible = True
            Worksheets("User Form").Shapes("SDBIP_Councillors").Visible = True
            Worksheets("User Form").Shapes("SDBIP_Emergency").Visible = True
            Worksheets("User Form").Shapes("SDBIP_Environment").Visible = True
            Worksheets("User Form").Shapes("SDBIP_Expenditure").Visible = True
            Worksheets("User Form").Shapes("SDBIP_BTO").Visible = True
            Worksheets("User Form").Shapes("SDBIP_Financial").Visible = True
            Worksheets("User Form").Shapes("SDBIP_HR").Visible = True
            Worksheets("User Form").Shapes("SDBIP_IDP").Visible = True
            Worksheets("User Form").Shapes("SDBIP_ICT").Visible = True
            Worksheets("User Form").Shapes("SDBIP_LED").Visible = True
            Worksheets("User Form").Shapes("SDBIP_Mun_Health").Visible = True
            Worksheets("User Form").Shapes("SDBIP_MM").Visible = True
            Worksheets("User Form").Shapes("SDBIP_Performance").Visible = True
            Worksheets("User Form").Shapes("SDBIP_Revenue").Visible = True
            Worksheets("User Form").Shapes("SDBIP_Risk").Visible = True
            Worksheets("User Form").Shapes("SDBIP_Roads").Visible = True
            Worksheets("User Form").Shapes("SDBIP_SCM").Visible = True
        Else
            Worksheets("User Form").Shapes("SDBIP_Auditing").Visible = False
            Worksheets("User Form").Shapes("SDBIP_CFO").Visible = False
            Worksheets("User Form").Shapes("SDBIP_Committee").Visible = False
            Worksheets("User Form").Shapes("SDBIP_Community_Director").Visible = False
            Worksheets("User Form").Shapes("SDBIP_Councillors").Visible = False
            Worksheets("User Form").Shapes("SDBIP_Emergency").Visible = False
            Worksheets("User Form").Shapes("SDBIP_Environment").Visible = False
            Worksheets("User Form").Shapes("SDBIP_Expenditure").Visible = False
            Worksheets("User Form").Shapes("SDBIP_BTO").Visible = False
            Worksheets("User Form").Shapes("SDBIP_Financial").Visible = False
            Worksheets("User Form").Shapes("SDBIP_HR").Visible = False
            Worksheets("User Form").Shapes("SDBIP_IDP").Visible = False
            Worksheets("User Form").Shapes("SDBIP_ICT").Visible = False
            Worksheets("User Form").Shapes("SDBIP_LED").Visible = False
            Worksheets("User Form").Shapes("SDBIP_Mun_Health").Visible = False
            Worksheets("User Form").Shapes("SDBIP_MM").Visible = False
            Worksheets("User Form").Shapes("SDBIP_Performance").Visible = False
            Worksheets("User Form").Shapes("SDBIP_Revenue").Visible = False
            Worksheets("User Form").Shapes("SDBIP_Risk").Visible = False
            Worksheets("User Form").Shapes("SDBIP_Roads").Visible = False
            Worksheets("User Form").Shapes("SDBIP_SCM").Visible = False
    End If

'   Performance Assist
    If Worksheets("User Form").Range("B162").Value = "Yes" Or Worksheets("User Form").Range("B162").Value = "yes" Then
            Worksheets("User Form").Shapes("Perf_Administrator").Visible = True
            Worksheets("User Form").Shapes("Perf_Assist_Administrator").Visible = True
            Worksheets("User Form").Shapes("Perf_Auditing").Visible = True
            Worksheets("User Form").Shapes("Perf_CFO").Visible = True
            Worksheets("User Form").Shapes("Perf_Committee").Visible = True
            Worksheets("User Form").Shapes("Perf_Community_Director").Visible = True
            Worksheets("User Form").Shapes("Perf_Councillors").Visible = True
            Worksheets("User Form").Shapes("Perf_Emergency").Visible = True
            Worksheets("User Form").Shapes("Perf_Environment").Visible = True
            Worksheets("User Form").Shapes("Perf_Expenditure").Visible = True
            Worksheets("User Form").Shapes("Perf_BTO").Visible = True
            Worksheets("User Form").Shapes("Perf_Financial").Visible = True
            Worksheets("User Form").Shapes("Perf_HR").Visible = True
            Worksheets("User Form").Shapes("Perf_IDP").Visible = True
            Worksheets("User Form").Shapes("Perf_ICT").Visible = True
            Worksheets("User Form").Shapes("Perf_LED").Visible = True
            Worksheets("User Form").Shapes("Perf_Mun_Health").Visible = True
            Worksheets("User Form").Shapes("Perf_MM").Visible = True
            Worksheets("User Form").Shapes("Perf_Performance").Visible = True
            Worksheets("User Form").Shapes("Perf_Revenue").Visible = True
            Worksheets("User Form").Shapes("Perf_Risk").Visible = True
            Worksheets("User Form").Shapes("Perf_Roads").Visible = True
            Worksheets("User Form").Shapes("Perf_SCM").Visible = True
        Else
            Worksheets("User Form").Shapes("Perf_Administrator").Visible = False
            Worksheets("User Form").Shapes("Perf_Assist_Administrator").Visible = False
            Worksheets("User Form").Shapes("Perf_Auditing").Visible = False
            Worksheets("User Form").Shapes("Perf_CFO").Visible = False
            Worksheets("User Form").Shapes("Perf_Committee").Visible = False
            Worksheets("User Form").Shapes("Perf_Community_Director").Visible = False
            Worksheets("User Form").Shapes("Perf_Councillors").Visible = False
            Worksheets("User Form").Shapes("Perf_Emergency").Visible = False
            Worksheets("User Form").Shapes("Perf_Environment").Visible = False
            Worksheets("User Form").Shapes("Perf_Expenditure").Visible = False
            Worksheets("User Form").Shapes("Perf_BTO").Visible = False
            Worksheets("User Form").Shapes("Perf_Financial").Visible = False
            Worksheets("User Form").Shapes("Perf_HR").Visible = False
            Worksheets("User Form").Shapes("Perf_IDP").Visible = False
            Worksheets("User Form").Shapes("Perf_ICT").Visible = False
            Worksheets("User Form").Shapes("Perf_LED").Visible = False
            Worksheets("User Form").Shapes("Perf_Mun_Health").Visible = False
            Worksheets("User Form").Shapes("Perf_MM").Visible = False
            Worksheets("User Form").Shapes("Perf_Performance").Visible = False
            Worksheets("User Form").Shapes("Perf_Revenue").Visible = False
            Worksheets("User Form").Shapes("Perf_Risk").Visible = False
            Worksheets("User Form").Shapes("Perf_Roads").Visible = False
            Worksheets("User Form").Shapes("Perf_SCM").Visible = False
    End If
    
End Sub

Originally "Worksheets("user Form")" was "ActiveSheet" (or something similar). I changed it to use the sheet's name, but it did not help. What can I do to fix this?
 
Sort by date Sort by votes
Thank you. I realise you are about 7 or 8 hours ahead of me :)
I will not be on the forum for most of the weekend, but I'll see what I can do. If all else fails, we can continue this on Monday.
 
Upvote 0

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
i am +10.00 time zone, plus an hour for daylight saving :) nearly 1am. but its friday and i dont have to be up quite so early tomorrow. but i am old and worn out so thats late enough for me
 
Upvote 0
yes thats good for me. is there a newer file than post #39
 
Upvote 0
Not sure what you mean. The value of B7 is what determines what must happen in the "Employee Information" area (range for that are is A6:E16).
 
Upvote 0
"Sub Worksheet_Change sub) that checks the value of B7 and then enters formulas or does other things...." and thats your error issue. i think i would simplify all that code and messing with inserting formulae. just get the values you need and stick them in. also i see that by combining the 2 sub, the starting test used for the checkboxes is not the same type as the other parts, so it needs to be fenced off, so to speak.


this is half cleaned up and runs...

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Vis As Boolean
    Application.ScreenUpdating = False
    With Sheets("User Form")
        If Target.Address = "$B$7" Then
    '       .Unprotect "abc123"
            Select Case .Range("B7").Value
                Case "New Account"
                    With Range("C14:E14").Validation
        '                Performance Contract dropdown
                        .Delete
                        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Data!$G$2:$G$3"
                        .IgnoreBlank = True
                        .InCellDropdown = True
                        .InputTitle = ""
                        .ErrorTitle = ""
                        .InputMessage = "Select ""Yes"" if the employee does not need any form of IT/phone/alarm code access, otherwise select ""No""."
                        .ErrorMessage = "Please select Yes or No"
                        .ShowInput = True
                        .ShowError = True
                    End With
                    With .Range("D7:E7,B15,B26,D9")
                        .Locked = True
                        .FormulaHidden = False
                    End With
                    With .Range("B9:B12,D10:D11")
                        .Locked = False
                        .FormulaHidden = False
                        .ClearContents
                    End With
                    Range("B8").Select

                Case "Termination of Account"
    '                    ActiveSheet.Unprotect "abc123"
    '                    Application.ScreenUpdating = False
                    With .Range("B9:B12,D7:E7,D9:E11,C14:E14")
                        .Locked = False
                        .FormulaHidden = False
                    End With
                    Range("D9").Select
                    Selection.Locked = False
                    Selection.FormulaHidden = False
                    Range("B9").Select
                    ActiveCell.FormulaR1C1 = _
                        "=IF(R8C2="""","""",IF(R7C2=""Termination of Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C7,""Unknown Employee"")))"
                    Range("B10").Select
                    ActiveCell.FormulaR1C1 = _
                        "=IF(R8C2="""","""",IF(R7C2=""Termination of Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C2,""Unknown Employee"")))"
                    Range("B11").Select
                    ActiveCell.FormulaR1C1 = _
                        "=IF(R8C2="""","""",IF(R7C2=""Termination of Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C8,""Unknown Employee"")))"
                    Range("B12:B13").Select
                    ActiveCell.FormulaR1C1 = _
                        "=IF(R8C2="""","""",IF(R7C2=""Termination of Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C9,""Unknown Employee"")))"
                    Range("D9").Select
                    ActiveCell.Formula2R1C1 = _
                        "=IF(R8C2="""","""",IF(R9C3<>"""",IF(XLOOKUP(R8C2,'User Data'!C6,'User Data'!C11,"""")=0,"""",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C11,"""")),""""))"
                    Range("D10:E10").Select
                    ActiveCell.FormulaR1C1 = _
                        "=IF(R8C2="""","""",IF(R7C2=""Termination of Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C14,""Unknown Employee"")))"
                    Range("D11:E11").Select
                    ActiveCell.FormulaR1C1 = _
                        "=IF(R8C2="""","""",IF(R7C2=""Termination of Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C12,""Unknown Employee"")))"
                    Range("B9:B13,D10:E11").Select
                    Range("D10:E11").Activate
                    Selection.Locked = True
                    Selection.FormulaHidden = False
                    Range("B8").Select

                    Case "Change Account"
    '                        ActiveSheet.Unprotect "abc123"
    '                        Application.ScreenUpdating = False
                        Range("B9:B12,D9:D11,C14:E14").Select
                        Range("C14").Activate
                        Selection.Locked = False
                        Selection.FormulaHidden = False
                        Selection.ClearContents
                        Range("B9").Select
                        ActiveCell.FormulaR1C1 = _
                            "=IF(R8C2="""","""",IF(R7C2=""Change Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C7,""Unknown Employee"")))"
                        Range("B10").Select
                        ActiveCell.FormulaR1C1 = _
                            "=IF(R8C2="""","""",IF(R7C2=""Change Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C2,""Unknown Employee"")))"
                        Range("B11").Select
                        ActiveCell.FormulaR1C1 = _
                            "=IF(R8C2="""","""",IF(R7C2=""Change Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C8,""Unknown Employee"")))"
                        Range("B9:B11").Select
                        Range("B9:B11").Activate
                        Selection.Locked = True
                        Selection.FormulaHidden = False
                        .Range("B8").Select
                    Case Else
                        With .Range("B9:B12,D9:E11")
                            .Locked = False
                            .FormulaHidden = False
                            .ClearContents
                        End With
                        .Range("B8").Select
            End Select

            With .Range("C14:E14").Validation
                .Delete
                .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
                :=xlBetween
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
            .Range("B8").Select
            
    '        ActiveSheet.Protect "abc123"
    '        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        ElseIf Target.Address = "$B$9" Then
            .Unprotect "abc123"
            If ActiveSheet.Range("B9").Value = "Contract" Or .Range("B9").Value = "Councillor" Then
                Range("D9:E9").Locked = False
                Range("D9:E9").FormulaHidden = False
                Range("D9:E9").ClearContents
                Range("B10").Select
            Else
                Range("D9:E9").Locked = True
                Range("D9:E9").FormulaHidden = False
                Range("B10").Select
            End If
    '        ActiveSheet.Protect "abc123"
    '        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        Else
            If UCase(Target.Value) = "YES" Then Vis = True Else Vis = False
        
        '   Performance Assist
            If Target.Address = "$B$160" Then
                HiLiteShapes "Perf_", Vis
            End If
        
        '   Eunomia
            If Target.Address = "$D$134" Then
                HiLiteShapes "Eunomia", Vis
            End If
        
        '   Alarm Code & Building Keys
            If Target.Address = "$B$56" Then
                HiLiteShapes "Alarm", Vis
            End If
        
        '   IT Equipment
            If Target.Address = "$E$50" Then
                HiLiteShapes "ITEquip", Vis
            End If
         
        '   Risk Management
            If Target.Address = "$B$134" Then
                HiLiteShapes "RiskMan", Vis
            End If
            
        '   SDBIP
            If Target.Address = "$B$147" Then
                HiLiteShapes "SDBIPAdmin", Vis
            End If
            Application.ScreenUpdating = True
        End If
    End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Hiding Tabs using specific value in Cell
Replies
4
Views
85
cboyce44
C
L
  • Solved
Worksheet Change_Event not working on cell value change
Replies
9
Views
493
Lil Stinker
L
S
  • Question Question
VBA Help Please
2
Replies
16
Views
1K
Peter_SSs
Peter_SSs
E
  • Question Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
521
offthelip
O
B
  • Solved
Combining vba
Replies
1
Views
505
Joe4
Joe4

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top