Hide rows based on prior setup sheet

[edjohn20]

I know there is VBA code to do this. I don't know VBA well enough to write it. However I know exactly what I want to do.

I have a worksheet 1 titled "Setup" and worksheet 2 titled "MMS PaaS Pricing 2024"

I have configurable in Setup B4 to B11. This field will be either YES or NO (chosen from drop down)

core
IF b4=NO the Hide "MMS PaaS Pricing 2024" rows 9-15
cm
IF b5=NO the Hide "MMS PaaS Pricing 2024" rows 16-21
sc
IF b6=NO the Hide "MMS PaaS Pricing 2024" rows 22-30
acm
IF b7=NO the Hide "MMS PaaS Pricing 2024" rows 31-38
ssm
IF b8=NO the Hide "MMS PaaS Pricing 2024" rows 39-46
lca
IF b9=NO the Hide "MMS PaaS Pricing 2024" rows 47-55
web
IF b10=NO the Hide "MMS PaaS Pricing 2024" rows 56-63
movr
IF b11=NO the Hide "MMS PaaS Pricing 2024" rows 64-69

There are also formulas and values within the cells to be hidden that need to be suppressed as well. Meaning if SC section is NO then the fees and values do not get included.

 

[rpaulson]

try this.

Sub Hide()

Dim rs As Worksheet
Set rs = Worksheet("MMS PaaS Pricing 2024")


rs.Rows("6:69").Hidden = False

If [B4] = "NO" Then rs.Rows("9:15").Hidden = True
If [B5] = "NO" Then rs.Rows("16:21").Hidden = True

'......add the rest as needed
End Sub

hth,
Ross

 

[edjohn20]

Thank you so much. I have one question regarding where this code should be applied. Should it be module 1 or attached to certain sheet? Also what will invoke the changes? Will it be live to changes in Setup b column or will I need to run macro?

 

[rpaulson]

it can reside in module1.

for now just add a shape to your sheet and assign the macro the the shape.
once you have it working then you can assign it the cells B4:B11) that, when changed, would fire off the macro

 

[edjohn20]

it can reside in module1.

for now just add a shape to your sheet and assign the macro the the shape.
once you have it working then you can assign it the cells B4:B11) that, when changed, would fire off the macro

Ok, it is giving me a compile error: sub or function not defined.

Is it due to the () at the top?

 

[edjohn20]

I will see if I can get some screen shots to show what I am seeing. I certainly appreciate your help

 

[edjohn20]

So maybe I changed something. I don't know. Does not look like I did. I am not sure if it is looking to worksheet "Setup" to determine b values but here is current code loaded in module one:

Sub Hide()

Dim rs As Worksheet
Set rs = Worksheet("MMS PaaS Pricing 2024")


rs.Rows("6:69").Hidden = False

If [B4] = "NO" Then rs.Rows("9:15").Hidden = True
If [B5] = "NO" Then rs.Rows("16:21").Hidden = True
If [B6] = "NO" Then rs.Rows("22:30").Hidden = True
If [B7] = "NO" Then rs.Rows("31:38").Hidden = True
If [B8] = "NO" Then rs.Rows("39:46").Hidden = True
If [B9] = "NO" Then rs.Rows("47:55").Hidden = True
If [B10] = "NO" Then rs.Rows("56:63").Hidden = True
If [B11] = "NO" Then rs.Rows("64:69").Hidden = True


'......add the rest as needed
End Sub

 

[rpaulson]

oops,

change
Set rs = Worksheet("MMS PaaS Pricing 2024")
to
Set rs = Worksheets("MMS PaaS Pricing 2024")

 

[edjohn20]

oops,

change
Set rs = Worksheet("MMS PaaS Pricing 2024")
to
Set rs = Worksheets("MMS PaaS Pricing 2024")

Oh, awesome. That works. Now to assign to button. Would it be form button or active X. I hate that I am asking all these questions, but I certainly appreciate the assistance.

 

[rpaulson]

just go to insert menu and select shape, pick any shape you want, right click and assign macro to it.