Adding new code to existing macro to account for locked/unlocked cells

C

ctbanker

New Member
Joined
Aug 26, 2015
Messages
26
Hi All,

I want to add code to an existing macro to account for locked/unlocked cells. In the sheet below, I want to only keep the manila colored cells with blue text as unlocked cells - everything else should be locked. My code breaks as soon as I lock/unlock cells. I've also attached the current code for reference. Thanks in advance!!

Finance Assessment Template_as of 6.20.23 V3.xlsm
ABCDEFGHIJKL
1Cash Outflow StreamsSelect from Dropdown:Notes:
2Capitalized Expenses (CapEx) àCapExProject will incur Capitalized Expense 
3Operating Expenses (OpEx) àOpExProject will incur Operating Expense 
4Post Project Operating Expenses (OpEx) àPost Project OpExAs result of the project will incur incremental OpEx after the project concludes 
6DirectDirect - Post Project OpEx 
7IndirectIndirect - Post Project OpEx 
8Contingent / Consultant Costs àContingent / Consultant CostsContingent and Consultant Costs: The project will require contingent and/or consultant resources 
9Internal Labor Costs àInternal Labor CostsThe project will require internal employee resources or dedicated time from existing and/or new employees to implement and/or upkeep 
11Based on Hours EntryBased on Hours Entry 
12Estimated SalaryEstimated Salary 
13Open EntryOpen Entry 
14
15Incremental Cash Outflows
16
18Investment Cash Flows (Capatalized)Cash Flow Characteristics2023 - Q12023 - Q22023 - Q32023 - Q42024202520262027CapEx Categorization
19Cash Flow TypesDivision/Project Booking Expense
20Cost of the asset/equipment/software developmentProject$ 1,000$ 2,000$ 3,000$ 4,000$ 5,000$ 6,000$ 7,000$ 8,000CapEx
21Set-up expenditures - shipping, installation, etc.IT$ 20,000$ -$ -CapEx
22Continued investment in asset over years, if anyLGRC$ 1$ 1$ -$ -$ -CapEx
23Sales tax on purchases$ -$ -$ -$ -CapEx
24Workforce Env access to member Env HW - PAW [EXAMPLE]$ -$ -$ -$ -CapEx
25Citrix Deployment by LK Method [EXAMPLE]$ -$ -$ -$ -CapEx
26[Project specific capitalized expense]$ -$ -$ -$ -CapEx
27[Project specific capitalized expense]$ -$ -$ -$ -CapEx
28
29Total - Investment Cash Flows (Capatalized)$ 21,001$ 2,001$ 3,000$ 4,000$ 5,000$ 6,000$ 7,000$ 8,000
30
32Operating Cash Flows (Expensed during Project Implementation)Cash Flow Characteristics2023 - Q12023 - Q22023 - Q32023 - Q42024202520262027OpEx Categorization
33Cash Flow TypesDivision/Project Booking Expense
34Expenses funded by department OpEx budgets Project$ 2,000$ -$ -$ -$ -$ -$ -$ - OpEx
35Legal expensesOperations$ -$ 4,000$ -$ -$ -$ -$ -$ - OpEx
36Fraud prevention expensesMarketing/Co-Op Affairs$ -$ -$ 6,000$ -$ -$ -$ -$ - OpEx
37Hiring/recruiting expenses$ -$ -$ -$ 8,000$ -$ -$ -$ - OpEx
38Data management costs$ -$ -$ -$ -$ 10,000$ -$ -$ - OpEx
39Incremental transaction-related expenses$ -$ -$ -$ -$ -$ 12,000$ -$ - OpEx
40M365 license upgrade (true up) [EXAMPLE]$ -$ -$ -$ -$ -$ -$ 14,000$ - OpEx
41Fiserv vendor support [EXAMPLE]$ -$ -$ -$ -$ -$ -$ -$ 16,000 OpEx
42[Project specific operating expense]$ -$ -$ -$ -$ -$ -$ -$ - OpEx
43[Project specific operating expense]$ -$ -$ -$ -$ -$ -$ -$ - OpEx
44
45Total - Operating Cash Flows$ 2,000$ 4,000$ 6,000$ 8,000$ 10,000$ 12,000$ 14,000$ 16,000
46
48Operating Cash Flows (Expensed after Project Completion)Cash Flow Characteristics2023 - Q12023 - Q22023 - Q32023 - Q42024202520262027 OpEx Categorization
49DirectDivision/Project Booking Expense
50License fees$ 1,500 OpEx
51On-going Vendor Contact$ 2,000 OpEx
52Rent$ 3,000 OpEx
53Maintenance$ 4,000 OpEx
54Computing Costs$ 5,000 OpEx
55Software/hardware maintenance $ 6,000 OpEx
56Credit/debit card production costs$ 70,000 OpEx
57Workforce environment HW - PAW [EXAMPLE]$ 8,000 OpEx
58Citrix Virtualization Licensing [EXAMPLE] OpEx
59Licensing cost of PIDE environment [EXAMPLE] OpEx
60[Project specific operating expenses] OpEx
61[Project specific operating expenses] OpEx
62
63Total - Direct Operating Cash Flows after Project Completion$ 1,500$ 2,000$ 3,000$ 4,000$ 5,000$ 6,000$ 70,000$ 8,000
B.CashOutflows
Cell Formulas
RangeFormula
F2F2=IF(AND(B2="N/A",SUM($C$20:$J$29)>0),"← REVIEW: There are inputs in CapEx Outflow Stream","")
F3F3=IF(AND(B3="N/A",SUM($C$34:$J$45)>0),"← REVIEW: There are inputs in OpEx Outflow Stream","")
F4F4=IF(AND(B4="N/A",OR(SUM($C$50:$J$63)>0,SUM($C$68:$J$74)>0)),"← REVIEW: There are inputs in OpEx Outflow Stream","")
F6F6=IF(AND(B6="N/A",SUM($C$50:$J$63)>0),"← REVIEW: There are inputs in Direct - Post Project OpEx Outflow Stream","")
F7F7=IF(AND(B7="N/A",SUM($C$68:$J$74)>0),"← REVIEW: There are inputs in Direct - Post Project OpEx Outflow Stream","")
F8F8=IF(AND(B8="N/A",SUM($C$79:$J$84)>0),"← REVIEW: There are inputs in Contingent/Consultant Outflow Stream","")
F9F9=IF(AND(B9="N/A",OR(SUM($C$91:$J$100)>0,SUM($C$105:$J$109)>0,SUM($C$115:$J$119)>0)),"← REVIEW: There are inputs in Internal Labor Costs Outflow Stream","")
F11F11=IF(AND(B11="N/A",SUM($C$91:$J$100)>0),"← REVIEW: There are inputs in Based on Hours Entry Outflow Stream","")
F12F12=IF(AND(B12="N/A",SUM($C$105:$J$109)>0),"← REVIEW: There are inputs in Based on Estimated Salary Outflow Stream","")
F13F13=IF(AND(B13="N/A",SUM($C$115:$J$119)>0),"← REVIEW: There are inputs in Open Entry Outflow Stream","")
C18,C48,C32C18=YEAR(A.ProjectSummary!$C$4)&" - Q1"
D18,D48,D32D18=YEAR(A.ProjectSummary!$C$4)&" - Q2"
E18,E48,E32E18=YEAR(A.ProjectSummary!$C$4)&" - Q3"
F18,F48,F32F18=YEAR(A.ProjectSummary!$C$4)&" - Q4"
G18,G48,G32G18=YEAR(A.ProjectSummary!$C$4)+1
H18:J18,H48:J48,H32:J32H18=G18+1
C29:J29C29=SUM(C20:C27)
C45:J45C45=SUM(C34:C43)
C63:J63C63=SUM(C50:C61)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B3Expression=AND($B$3="N/A",SUM($C$34:$J$45)>0)textNO
B9Expression=AND(B9="N/A",OR(SUM($C$91:$J$100)>0,SUM($C$105:$J$109)>0,SUM($C$115:$J$119)>0))textNO
B13Expression=AND($B$13="N/A",SUM($C$115:$J$119)>0)textNO
B12Expression=AND($B$12="N/A",SUM($C$105:$J$109)>0)textNO
B4Expression=AND($B$4="N/A",OR(SUM($C$50:$J$63)>0),SUM($C$68:$J$74)>0)textNO
B6Expression=AND($B$6="N/A",SUM($C$50:$J$63)>0)textNO
B11Expression=AND($B$11="N/A",SUM($C$91:$J$100)>0)textNO
B7Expression=AND($B$7="N/A",SUM($C$68:$J$74)>0)textNO
B2Expression=AND($B$2="N/A",SUM($C$20:$J$29)>0)textNO
B8Expression=AND($B$8="N/A",SUM($C$79:$J$84)>0)textNO
Cells with Data Validation
CellAllowCriteria
B11ListBased on Hours Entry, N/A
B12ListEstimated Salary, N/A
B13ListOpen Entry, N/A
B2ListCapEx, N/A
B3ListOpEx, N/A
B4:B5ListPost Project OpEx, N/A
B6ListDirect - Post Project OpEx, N/A
B7ListIndirect - Post Project OpEx, N/A
B8ListContingent / Consultant Costs, N/A
B9ListInternal Labor Costs, N/A
L50:L61ListOpEx
L34:L43ListOpEx
L20:L27ListCapEx
B20:B27List=DropDownControl!$A$2:$A$10
B34:B43List=DropDownControl!$A$2:$A$10
B50:B61List=DropDownControl!$A$2:$A$10


VBA Code: 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim TriggerRanges(1 To 10) As Range
    Dim i As Integer
    Set TriggerRanges(1) = Range("B2")
    Set TriggerRanges(2) = Range("B3")
    Set TriggerRanges(3) = Range("B4")
    Set TriggerRanges(4) = Range("B8")
    Set TriggerRanges(5) = Range("B9")
    Set TriggerRanges(6) = Range("B6")
    Set TriggerRanges(7) = Range("B7")
    Set TriggerRanges(8) = Range("B11")
    Set TriggerRanges(9) = Range("B12")
    Set TriggerRanges(10) = Range("B13")

   
    For i = 1 To 10
        If Not Application.Intersect(TriggerRanges(i), Target) Is Nothing Then
            If TriggerRanges(i).Value = "N/A" Then
                Range(GetInputRange(i)).EntireRow.Hidden = True
            Else
                Range(GetInputRange(i)).EntireRow.Hidden = False
            End If
        End If
    Next i
End Sub
Function GetInputRange(ByVal index As Integer) As String
    Select Case index
        Case 1
            GetInputRange = "Investment_Cash_Flows"
        Case 2
            GetInputRange = "Operating_Cash_Flows"
        Case 3
            GetInputRange = "Post_Project_OpEx_Dropdown"
        Case 4
            GetInputRange = "Contingent_Consultant_Costs_Hours_Entry"
        Case 5
            GetInputRange = "Internal_Labor_Costs_Dropdown"
        Case 6
            GetInputRange = "Direct_Operating_Cash_Flows_After_Project_Completion"
        Case 7
            GetInputRange = "Indirect_Operating_Cash_Flows_After_Project_Completion"
        Case 8
            GetInputRange = "Internal_Labor_Costs_Hours_Entry"
        Case 9
            GetInputRange = "Internal_Labor_Costs_Estimated_Salaries"
        Case 10
            GetInputRange = "Internal_Labor_Costs_Open_Entry"
            

            
    End Select
End Function
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
If the sheet is protected, add the lines highlighted in blue.
Change "abc" to the password of your sheet.


Rich (BB code): 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim TriggerRanges(1 To 10) As Range
    Dim i As Integer
    Set TriggerRanges(1) = Range("B2")
    Set TriggerRanges(2) = Range("B3")
    Set TriggerRanges(3) = Range("B4")
    Set TriggerRanges(4) = Range("B8")
    Set TriggerRanges(5) = Range("B9")
    Set TriggerRanges(6) = Range("B6")
    Set TriggerRanges(7) = Range("B7")
    Set TriggerRanges(8) = Range("B11")
    Set TriggerRanges(9) = Range("B12")
    Set TriggerRanges(10) = Range("B13")

    ActiveSheet.Unprotect "abc"
   
    For i = 1 To 10
        If Not Application.Intersect(TriggerRanges(i), Target) Is Nothing Then
            If TriggerRanges(i).Value = "N/A" Then
                Range(GetInputRange(i)).EntireRow.Hidden = True
            Else
                Range(GetInputRange(i)).EntireRow.Hidden = False
            End If
        End If
    Next i

    ActiveSheet.Protect "abc"

End Sub


--------------
Cordially
Dante Amor
--------------​
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Combining similar Private Sub Worksheet Codes (VBA)
2
Replies
18
Views
673
Jimmypop
Jimmypop
k3yn0t3
  • Question
Simple Macro to Copy/Paste Cells onto Another Sheet
Replies
2
Views
214
kevin9999
K
T
  • Question
Is it Possible to Freeze a Selection?
Replies
2
Views
217
Jaafar Tribak
Jaafar Tribak
MrDB4Excel
  • Solved
If Exist in a Column Range
Replies
8
Views
938
DanteAmor
DanteAmor
C
  • Question
VBA Macros to Copy Specific Cells Values from Multiple Sheets to Specific Cells in Summary Sheets
Replies
2
Views
292
chittunoo
C

Forum statistics

Threads
1,215,071
Messages
6,122,963
Members
449,094
Latest member
Anshu121

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