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?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
hi. second issue first.
is there a sheet tab called "User Form" the subscript error is coming from an unknown sheet name
 
Upvote 0
Hi diddi. Yes, VBA I do have a sheet called "User Form". That is the main sheet where the code resides.
 
Upvote 0
ok. i am just doing a cleanup of your code. reducing the clutter a bit. give my 10 mins. working with another user. up to post about 160 i think :)
 
Upvote 0
Thank you. My VBA knowledge extends to basically finding code on Google, copying it and making minor modifications :)
 
Upvote 0
thats fine. i have some speed ups i have done. i have a task for you while i finish up. could you find some of your shapes and rename them slightly pls...
this set:
.Shapes("Barrydale").Visible = True
.Shapes("BredasdorpAnnex").Visible = True
.Shapes("BredasdorpFire").Visible = True
.Shapes("BredasdorpHeadOffice").Visible = True
.Shapes("BredasdorpRoads").Visible = True
.Shapes("BredasdorpSCM").Visible = True
.Shapes("BredasdorpWorkshop").Visible = True
.Shapes("CaledonFire").Visible = True
.Shapes("CaledonHealth").Visible = True
.Shapes("CaledonRoads").Visible = True
.Shapes("DieDam").Visible = True
.Shapes("GrabouwFire").Visible = True
.Shapes("GrabouwHealth").Visible = True
.Shapes("Hermanus").Visible = True
.Shapes("Karwyderskraal").Visible = True
.Shapes("Kleinmond").Visible = True
.Shapes("Struisbaai").Visible = True
.Shapes("SwellendamFire").Visible = True
.Shapes("SwellendamHealth").Visible = True
.Shapes("SwellendamRoads").Visible = True
.Shapes("Uilenkraalsmond").Visible = True
.Shapes("Villiersdorp").Visible = True

change the shape name on your worksheet (dont worry about your code) by Adding "Alarm_"
"Barrydale" ---> "Alarm_Barrydale"
 
Upvote 0
vg... so there are a couple more of them to do, but that was the big one. heres the new (skinny) version of your code

VBA Code: 
Private Sub Worksheet_Calculate()
    With Sheets("User Form")
    '   SDBIP - KPI Owner
        If .Range("G153").Value = True Then
            HiLiteShapes ("SDBIP")
        End If
    
    '   Performance Assist
        If UCase(.Range("B162").Value) = "YES" Then
            HiLiteShapes ("Perf_")
        End If
    
    '   Eunomia
        If UCase(.Range("D136").Value) = "YES" Then
            HiLiteShapes ("Eunomia")
        End If
    
    '   Risk Management - Action Owner
        If .Range("G140").Value = True Then
            HiLiteShapes ("Risk")
        End If
    
    '   Alarm Code & Building Keys
        If UCase(.Range("B55").Value) = "YES" Then
            HiLiteShapes ("Alarm")
        End If
    
    '   IT Equipment
        If UCase(.Range("E50").Value) = "YES" Then
            '  change the shape names in this section to "ITEquip_Laptop" etc then you can use this code
            '  HiLiteShapes ("ITEquip") and dump the rest :)
                .Shapes("Laptop").Visible = True
                .Shapes("Desktop").Visible = True
                .Shapes("Other").Visible = True
            Else
                .Shapes("Laptop").Visible = False
                .Shapes("Desktop").Visible = False
                .Shapes("Other").Visible = False
        End If
        
    '   Risk Management
        If .Range("B136").Value = "Yes" Or .Range("B136").Value = "yes" Then
            '  change the shape names in this section to "Risk_Administrator" then you can use this code
            '  HiLiteShapes ("RiskMan_Administrator") and dump the rest :)
                .Shapes("Risk_Administrator").Visible = True
                .Shapes("Risk_Assist_Administrator").Visible = True
                .Shapes("Risk_Risk_Owner").Visible = True
                .Shapes("Risk_Action_Owner").Visible = True
            Else
                .Shapes("Risk_Administrator").Visible = False
                .Shapes("Risk_Assist_Administrator").Visible = False
                .Shapes("Risk_Risk_Owner").Visible = False
                .Shapes("Risk_Action_Owner").Visible = False
        End If
    
    
    '   SDBIP
        If .Range("B149").Value = "Yes" Or .Range("B149").Value = "yes" Then
            '  change the shape names in this section to "SDBIP_Administrator" then you can use this code
            '  HiLiteShapes ("SDBIPAdmin_Administrator") and dump the rest :)
                .Shapes("SDBIP_Administrator").Visible = True
                .Shapes("SDBIP_Assist_Administrator").Visible = True
                .Shapes("SDBIP_HOD").Visible = True
                .Shapes("SDBIP_KPI_Owner").Visible = True
            Else
                .Shapes("SDBIP_Administrator").Visible = False
                .Shapes("SDBIP_Assist_Administrator").Visible = False
                .Shapes("SDBIP_HOD").Visible = False
                .Shapes("SDBIP_KPI_Owner").Visible = False
        End If
    
    End With
End Sub

Sub HiLiteShapes(MyStr As String)
    Dim MyShape As Shape
    
    For Each MyShape In ActiveSheet.Shapes
        If Left(Shape.Name, Len(MyStr)) = MyStr Then
            Shape.Name.Visible = True
        Else
            Shape.Name.Visible = False
        End If
    Next MyShape
End Sub
 
Upvote 0
Thank you very much. I'm not sure I understand your comments in the code though. What do you mean with "' HiLiteShapes ("RiskMan_Administrator") and dump the rest :)"? It also does not contain all the shape names. There are also sections that I think are missing (Performancec Assist in block B162 for example), unless your intention was for me to make a copy of the Risk Management code and just modify it.
 
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
156
cboyce44
C
L
  • Solved
Worksheet Change_Event not working on cell value change
Replies
9
Views
512
Lil Stinker
L
J
  • Question Question
Copy Picture - VBA
2
Replies
10
Views
271
Logit
Logit
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
545
offthelip
O

Forum statistics

Threads
1,224,818
Messages
6,181,151
Members
453,021
Latest member
Justyna P

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