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
As requested.

Dropbox

www.dropbox.com www.dropbox.com

The form is still in its early stages. I still have to figure out a way for Excel to change "yes" and any other version of "yEs" into "Yes", since VBA is case sensitive. The code I used for this is commented out, since it causes Excel to go into a "calculate" hang. I'm guessing there is a loop somewhere. I'm not saying this to ask you to fix that as well, just so that you know why I have commented out that piece of code :) .

Thank you for your help so far.
 
Upvote 0

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
if you are around now i can take a look
 
Upvote 0
looking at this area as an example.

1615457134765.png


when i choose NO should all the checkboxes disappear?, and yes, they are there
 
Upvote 0
That is correct. If "no" is selected, the area should be blank, otherwise it should show the blocks.
 
Upvote 0
ok, so we need to rejig your logic a bit... give me a few moments
 
Upvote 0
so here is a rejog of your code. fisrtly, rename your current worksheet_change sub to anything because this new sub uses the worksheet_change event. this code gets the checkboxes hiding / showing. take a look, and if you are happy we can move on :)

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

'   SDBIP
    If Target.Address = "$B$147" Then
        HiLiteShapes "SDBIPAdmin", Vis
    End If
End Sub

Sub HiLiteShapes(MyStr As String, IsVis As Boolean)
    Dim MyShape As Shape
   
    For Each MyShape In ActiveSheet.Shapes
        If Left(MyShape.Name, Len(MyStr)) = MyStr Then
            MyShape.Visible = IsVis
        End If
    Next MyShape
End Sub
 
Upvote 0
Apologies. I did not get a notification that you put a reply here. I got a bunch of errors when putting your code in, so I removed all my code and put it in a notepad file. I'll incorporate it back into Excel later. Now I have no errors.

It looks like the checkboxes got grouped, but I have no idea how. There are some that are grouped that should not be, though I can do the split myself once I know how, unless you want me to list what must get split?
 
Upvote 0
the grouping is based on the names if the controls. thats why i need them changed...
you can regroup however you want :)
let me know when you are ready. but it is getting late again :)

its skinny code now LOL
 
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,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

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