VBA:Toggle hiding rows based on cell value

[kemidan51]

Ok I have a spreadsheet that I am developing for use at work for Capability,

I have a cell G8 that using a drop down list for 3 variables "Regular" "Max limit" "Min Limit"

In a series of calculation rows (because theres 30 sets or the same calculation in each row) I have a row for Maximum spec limit Row 22 Nominal Spec limit Row 23 and Minimum Spec limit Row 24.

What i want the macro to do is to always and actively run with out having to click a button. So when the user picks "Min Limit" from G8 it will hide row 22 and in the same moment if I pick "Max limit" from G8 it will unhide 22 and hide row 24, ALSO if they pick "Regular" again from G8 it will unhide which ever rows were previously hidden and so that all 3 rows are showing.

The purpose for me to hide these is for visual purposes to hide the actual line of data on the graph because its not valid

Here's what we tried. We triad Radio buttons and it works HOWEVER the cells in the rows that would remain unhidden have IF statements to change math formulas AS WELL if cell G8 = to specific value inside the dropdown list. Radio buttons do not allow the cell values to change

Can anyone help me out?

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "G8" Then
    Select Case LCase(Target.Value)
        Case "max limit": Rows(24).Hidden = True: Rows(22).Hidden = False
        Case "min limit": Rows(24).Hidden = False: Rows(22).Hidden = True
        Case Else: Rows("22:24").Hidden = False
    End Select
End If
End Sub

 

[kemidan51]

I cant seam to copy and paste your code with and running it with out it asking me to save it under a name and creating a new sub line thats blank. its like its not recognizing it as a named macro. what could i be doing wrong?

 

[VoG]

It is event code that goes in the sheet's code module. Right click the sheet tab, select View Code and paste in the code.

Press ALT + Q to close the code window then try changing the value of G8.

 

[kemidan51]

Ok I saw the words "Select Case Lcase" is that looking for "lowercase" letters? if so Ill have to apologize but The first letters are capitalized does that make a difference?

Also how would it change the syntax if i were to tell you the values would change to "Regular (±)" "One Sided (+)" and "One Sided (-)" per my bosses request

 

[kemidan51]

OK VoG just to try it out i added to the datavalidation list for G8 the words max limit and min limit and the code works!!! soo what do i need to do to get ti to read for "One Sided (-)" and "One Sided (+)"? ver batim

 

[VoG]

Maybe like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "G8" Then
    Select Case LCase(Target.Value)
        Case "one sided (+)": Rows(24).Hidden = True: Rows(22).Hidden = False
        Case "one sided (-)": Rows(24).Hidden = False: Rows(22).Hidden = True
        Case Else: Rows("22:24").Hidden = False
    End Select
End If
End Sub

The LCase() makes the test case-insensitive. It doesn't matter what the actual case is in your drop down.

 

[kemidan51]

NM!! I found it! i just remove Lcase from the code and it works just the way i want it too Thanks VoG