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'll play around with different groupings.
Quick question though. Does the "vis" refer to "visible"?
VBA Code: 
    If Target.Address = "$G$140" Then
        HiLiteShapes "Risk", Vis
    End If
 
Upvote 0

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
yes. Vis is a boolean variable (True or false) it is passed to HiLiteShapes which uses the info of (which group) and (visible or not) to show the checkboxes. big code saver. 1 code for 100+ checkboxes
 
Last edited:
Upvote 0
Ah I did not notice the Dim Vis As Boolean in the second row.
I have split the groups as I need them (Risk Management Action Owner and SDBIP KPI Owner in the code below). These are triggered when G138 and G151 respectively becomes TRUE. I have tick boxes linked to G138 and G151 which turns the value of the block to true or false, based on the tick box condition. It does not work at the moment, I think it is because the text is "true" and not "yes". I have tested this and the correct tick boxes does appear/disappear when yes or no is entered, but not when it is true or false.

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Vis As Boolean
    If UCase(Target.Value) = "YES" Then Vis = True Else Vis = False
   
'   SDBIP - KPI Owner
    If Target.Address = "$G$153" Then
        HiLiteShapes "SDBIP", Vis
    End If

'   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

'   Risk Management - Action Owner
    If Target.Address = "$G$140" Then
        HiLiteShapes "Risk", 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
    
'   Risk Management Action Owner
    If Target.Address = "$G$138" Then
        HiLiteShapes "RiskOwn", Vis
    End If
    
'   SDBIP
    If Target.Address = "$B$147" Then
        HiLiteShapes "SDBIPAdmin", Vis
    End If
    
'   SDBIP KPI Owner
    If Target.Address = "$G$151" Then
        HiLiteShapes "SDBIPOwn", Vis
    End If
    
End Sub

I played around with it some more and tried the following without result:

VBA Code: 
    If UCase(Target.Value) = "YES" Or True Then Vis = True Else Vis = False
VBA Code: 
    If UCase(Target.Value) = "YES" Or "TRUE" Then Vis = True Else Vis = False
VBA Code: 
    If UCase(Target.Value) = "YES" Then Vis = True Else If UCase(Target.Value) = "true" Then Vis = True Else Vis = False

I reverted the code back to your original, until you can read this post:

VBA Code: 
    If UCase(Target.Value) = "YES" Then Vis = True Else Vis = False
 
Upvote 0
Don't know why there is a time limit on the edit function of a post. Oh well.
There were duplicate pieces of code in my previous post and the duplicate referred to incorrect cells. I removed them.
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Vis As Boolean
    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
    
'   Risk Management Action Owner
    If Target.Address = "$G$138" Then
        HiLiteShapes "RiskOwn", Vis
    End If
    
'   SDBIP
    If Target.Address = "$B$147" Then
        HiLiteShapes "SDBIPAdmin", Vis
    End If
    
'   SDBIP KPI Owner
    If Target.Address = "$G$151" Then
        HiLiteShapes "SDBIPOwn", Vis
    End If
    
End Sub
 
Upvote 0
this sorts the problem... pull the 2 "G" reference ones out of that sub and insert this special handler for them. the worksheet_change event does not see checkbox events

VBA Code: 
Sub RiskMan_Action_Owner_Change()
'   Risk Management - Action Owner
    HiLiteShapes "Risk", Range("G138")
End Sub

Sub SDBIPAdmin_KPI_Owner_Change()
'   SDBIP - KPI Owner
    HiLiteShapes "SDBIP", Range("G151")
End Sub
 
Upvote 0
Ok. I removed the two pieces of code and pasted your code in a separate sub. I also corrected the prefix, since I changed the groupings yesterday and corrected the referenced cell.
VBA Code: 
Sub RiskMan_Action_Owner_Change()
'   Risk Management - Action Owner
    HiLiteShapes "RiskOwn", Range("G139")
End Sub

Sub SDBIPAdmin_KPI_Owner_Change()
'   SDBIP - KPI Owner
    HiLiteShapes "SDBIPOwn", Range("G152")
End Sub

As far as I understand, this code will not run automatically. The code works well when ran manually though. Will it work if we use a "call <macro>" entry in the Private Sub Worksheet_change?
Edit: Nevermind, calling the macro did not work.
 
Upvote 0
oh, yeh. in design mode, select the checkbox and assign macro...
1615533755033.png


tricky one there :)
 
Upvote 0
you had best send me the new version if there are still some things to sort
 
Upvote 0
Ah yes, I forgot you can assign them macros as well. Thank you. That resolves issue number 2 from my original post, now only issue number 1 remains. The remaining issue is that I have a macro (also in the Private Sub Worksheet_Change sub) that checks the value of B7 and then enters formulas or does other things. Combining it with your new code now produces a mismatch error. It did not do this with my old (and admittedly bloated) code. If I remove your code, it works, as long as the protect and unprotect lines are not commented out. As soon as I remove the comment to make the code work, it tells me it cant lock that cell.
Here is the updated file:

Dropbox

www.dropbox.com www.dropbox.com
 
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
107
cboyce44
C
L
  • Solved
Worksheet Change_Event not working on cell value change
Replies
9
Views
505
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
525
offthelip
O
B
  • Solved
Combining vba
Replies
1
Views
516
Joe4
Joe4

Forum statistics

Threads
1,223,891
Messages
6,175,229
Members
452,621
Latest member
Laura_PinksBTHFT

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