Need help Condensing and Fixing Macro Code

[evxret]

Hi, Im fairly new to VBA and have trouble writing code in the most efficient way, so I want to know if there is a way to simplify this:
I have a table at the top of my sheet which has different identifiers for different portions of my dashboard, (Etc. if Verizon is checked no, Verizon portion of the dashboard hides)
I have written these all separate, and after adding a macro that adds protections to my sheet, the previous written macros aren't working.
I attempted adding lines to Unprotect and Re-Protect the worksheet before the "EntireRow.Hidden" is called, but it is still not working.
Any help appreciated! Heres my code.

Private Sub worksheet_change(ByVal Target As Range)
Set Target = Range("E5")

If Target.Value = "Yes" Then
 Call CommissUnhide
End If

If Target.Value = "No" Then
Call CommissHide
End If

Set Target = Range("E6")
If Target.Value = "Yes" Then
 Call OtherUnhide
End If

If Target.Value = "No" Then
Call OtherHide
End If

Set Target = Range("E7")
If Target.Value = "Yes" Then
 Call ThreePLUnhide
End If

If Target.Value = "No" Then
Call ThreePLHide
End If

Set Target = Range("E8")
If Target.Value = "Yes" Then
 Call HardwareUnhide
End If

If Target.Value = "No" Then
Call HardwareHide
End If

Set Target = Range("E9")
If Target.Value = "Yes" Then
 Call ServicesUnhide
End If

If Target.Value = "No" Then
Call ServicesHide
End If
End Sub

Sub CommissUnhide()
    ActiveSheet.UnProtect "abc"
    ActiveSheet.Rows("12:16").EntireRow.Hidden = False
    ActiveSheet.Protect "abc"
End Sub


Sub CommissHide()
    ActiveSheet.UnProtect "abc"
    ActiveSheet.Rows("12:16").EntireRow.Hidden = True
    ActiveSheet.Protect "abc"
End Sub

Sub OtherUnhide()
    ActiveSheet.UnProtect "abc"
    ActiveSheet.Rows("17:21").EntireRow.Hidden = False
    ActiveSheet.Protect "abc"
End Sub


Sub OtherHide()
    ActiveSheet.UnProtect "abc"
    ActiveSheet.Rows("17:21").EntireRow.Hidden = True
    ActiveSheet.Protect "abc"
End Sub


Sub ThreePLUnhide()
    ActiveSheet.UnProtect "abc"
    ActiveSheet.Rows("22:26").EntireRow.Hidden = False
    ActiveSheet.Protect "abc"
End Sub


Sub ThreePLHide()
    ActiveSheet.UnProtect "abc"
    ActiveSheet.Rows("22:26").EntireRow.Hidden = True
    ActiveSheet.Protect "abc"
End Sub


Sub HardwareUnhide()
    ActiveSheet.UnProtect "abc"
    ActiveSheet.Rows("27:31").EntireRow.Hidden = False
    ActiveSheet.Protect "abc"
End Sub


Sub HardwareHide()
    ActiveSheet.UnProtect "abc"
    ActiveSheet.Rows("27:31").EntireRow.Hidden = True
    ActiveSheet.Protect "abc"
End Sub


Sub ServicesUnhide()
    ActiveSheet.UnProtect "abc"
    ActiveSheet.Rows("32:60").EntireRow.Hidden = False
    ActiveSheet.Protect "abc"
End Sub


Sub ServicesHide()
    ActiveSheet.UnProtect "abc"
    ActiveSheet.Rows("32:60").EntireRow.Hidden = True
    ActiveSheet.Protect "abc"
End Sub

 

[Joe4]

OK, I think you may be a little confused on how to use "Worksheet_Change" event procedure code.
This is special VBA code that runs automatically when placed in a Sheet module when some value is manually updated on the sheet.
(So key takeaway is that these MUST be placed in the appropriate Sheet module in the VB Editor).

The second thing, is you do NOT set the Target range. Target is a parameter (input) of the procedure.
Quite literally, it is the range that was just updated that triggered the code to run automatically.
So, you do NOT set it, but rather you check it to see if the range updated is the range you are looking for.

So if you wanted to do something if cell E5 is updated, you could do that a few ways, such as like this:

If Target.Address = Range("E5").Address Then

Or something like this:

If Intersect(Target, Range("E5")) is Nothing Then Exit Sub

which basically says if the updated cell is NOT cell E5, then exit the sub without doing anything (then you would place what you want to do if it is cell E5 under this line of code).

 

[evxret]

OK, I think you may be a little confused on how to use "Worksheet_Change" event procedure code.
This is special VBA code that runs automatically when placed in a Sheet module when some value is manually updated on the sheet.
(So key takeaway is that these MUST be placed in the appropriate Sheet module in the VB Editor).

The second thing, is you do NOT set the Target range. Target is a parameter (input) of the procedure.
Quite literally, it is the range that was just updated that triggered the code to run automatically.
So, you do NOT set it, but rather you check it to see if the range updated is the range you are looking for.

So if you wanted to do something if cell E5 is updated, you could do that a few ways, such as like this:

If Target.Address = Range("E5").Address Then

Or something like this:

If Intersect(Target, Range("E5")) is Nothing Then Exit Sub

which basically says if the updated cell is NOT cell E5, then exit the sub without doing anything (then you would place what you want to do if it is cell E5 under this line of code).

I believe Worksheet_Change is the right event procedure for my case? I will add my table in showing what is is doing.

Macro_Expense_Sheet_DRAFT.xlsm
	D	E
2	*Order Details:
3	Customer:	CustomerName
4	Supplier:	Verizon
5	Commissions:	Yes
6	SPIFF/Other:	Yes
7	3PL:	Yes
8	Hardware:	Yes
9	Services/OP Expenses:	Yes
PreSalesForm

The table above will hide certain sections whether or not the cell to the right of it says "Yes" or "No". I don't just want it to update any time a change is made, rather want it to do something specific when a certain string of text is input in that cell, does this make sense? Sorry I'm not the best at explaining.

 

[Joe4]

I believe Worksheet_Change is the right event procedure for my case? I will add my table in showing what is is doing.

Macro_Expense_Sheet_DRAFT.xlsm
	D	E
2	*Order Details:
3	Customer:	CustomerName
4	Supplier:	Verizon
5	Commissions:	Yes
6	SPIFF/Other:	Yes
7	3PL:	Yes
8	Hardware:	Yes
9	Services/OP Expenses:	Yes
PreSalesForm

The table above will hide certain sections whether or not the cell to the right of it says "Yes" or "No". I don't just want it to update any time a change is made, rather want it to do something specific when a certain string of text is input in that cell, does this make sense? Sorry I'm not the best at explaining.

Just explain your exact condition, in detail (generalities aren't too helpful).

When do you want it to run? When what cell is updated to what?
And what do you want to happen when that condition is met?