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

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Thank you. I'm seeing a few lines/functions in the code that I'm not familiar with, but we can discuss that when you are ready to.
 
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