Macro not firing when cell value changes

[billfinn]

Good morning!
I am trying to get this macro to fire when the value in cell F31 changes. The macro hides and un-hides specific rows based on the value in F31. F31 values are changed by a pull down menu. I'm trying to hide all values not pertaining to the choice made from the pull down regarding roof slope. I did insert the code in the worksheet where the pull down makes the changes. I change the values in F31 and nothing happens. I would greatly appreciate any input or suggestions regarding how to get this working.
Thanks very much for any help you could render
Bill

Sub HideRows()

Sheets("QUICK QUOTE").Select


Rows.EntireRow.Hidden = False


If F31 = "4:12" Then
    Rows("164:171").EntireRow.Hidden = False
    Rows("173:234").EntireRow.Hidden = True
If F31 = ("5:12") Then
    Rows("173:180").EntireRow.Hidden = False
    Rows("164:172").EntireRow.Hidden = True
    Rows("181:234").EntireRow.Hidden = True
If F31 = ("6:12") Then
    Rows("182:189").EntireRow.Hidden = False
    Rows("164:181").EntireRow.Hidden = True
    Rows("190:234").EntireRow.Hidden = True
If F31 = ("7:12") Then
    Rows("191:198").EntireRow.Hidden = False
    Rows("164:190").EntireRow.Hidden = True
    Rows("199:234").EntireRow.Hidden = True
If F31 = ("8:12") Then
    Rows("200:207").EntireRow.Hidden = False
    Rows("164:199").EntireRow.Hidden = True
    Rows("208:234").EntireRow.Hidden = True
If F31 = ("9:12") Then
    Rows("209:216").EntireRow.Hidden = False
    Rows("164:208").EntireRow.Hidden = True
    Rows("217:234").EntireRow.Hidden = True
If F31 = ("10:12") Then
    Rows("218:225").EntireRow.Hidden = False
    Rows("164:217").EntireRow.Hidden = True
    Rows("226:234").EntireRow.Hidden = True
If F31 = ("12:12") Then
Rows("227:234").EntireRow.Hidden = False
    Rows("164:226").EntireRow.Hidden = True
End If
End Sub

 

[Joe4]

In order to get VBA code to run automatically, you need to place it an Event Procedure, which runs automatically, upon some event firing. In this case, you want to use the "Worksheet_Change" event procedure, which runs when a cell value is manually updated.

However, I also think that you have some coding issues too.
Can you explain exactly what this line is supposed to do?

If F31 = "4:12" Then

Right now, it is checking for a variable named "F31" and check to see if it is equal to the literal text string "4:12". I doubt that what you had in mind.
To check the value of the cell F31, you would need to use Range("F31").
But I am not sure what you are trying to do with the "4:12" part.

 

[billfinn]

Joe4,
Thanks for responding.
what it supposed to happen is that when the value in cell F31 changes via the pull down menu a group or two groups of rows need to be hidden. If it changes again, there are different rows that need to be hidden
Thanks,Bill

 

[Joe4]

I understand that, I just don't understand what the details are.

If you have a whole bunch of IF statements. Please explain, in detail, EXACTLY what it is supposed to be checking.

If F31 = "4:12" Then

Assuming that you really mean Range("F31") (as I mentioned in my previous post), please explain what you are trying to do with the "4:12" part. Are you really look for a literal text entry of "4:12" in cell F31, or are you trying to do something else (perhaps with a range)?

Also, is this cell "F31" that you are checking actually on your "QUICK QUOTE" sheet, or another sheet?

You are also missing a whole bunch of END IF statements. Since you are not using "ELSE", every single IF statement would need a closing END IF statement.
However, we may be able to simplify and use a CASE statement, once we figure out exactly what you are trying to do with the "4:12" part.

Lastly, since you start off by unhiding all the rows in the entire worksheet at the top, there is no need for the individual row unhide statements within each IF. It is redundant and unnecessary.

 

[billfinn]

Joe4,
I am indeed trying to see if cell F31 in the worksheet QUICK QUOTES in the Estimator workbook has a text entry of 4:12
I also thought that anytime someone changed cell F31 using the pull down menu that the rows containing all the details for a 4:12 or 5:12 roof pitch would need to now be visible

 

[Joe4]

OK, right-click on the sheet tab name at the bottom of the "QUICK QUOTE" worksheet, select "View Code", and paste this code in the resulting VB Editor window. This should do what you want.

Private Sub Worksheet_Change(ByVal Target As Range)

    Rows.EntireRow.Hidden = False

    Select Case Range("F31")
        Case "4:12"
            Rows("173:234").EntireRow.Hidden = True
        Case "5:12"
            Rows("164:172").EntireRow.Hidden = True
            Rows("181:234").EntireRow.Hidden = True
        Case "6:12"
            Rows("164:181").EntireRow.Hidden = True
            Rows("190:234").EntireRow.Hidden = True
        Case "7:12"
            Rows("164:190").EntireRow.Hidden = True
            Rows("199:234").EntireRow.Hidden = True
        Case "8:12"
            Rows("164:199").EntireRow.Hidden = True
            Rows("208:234").EntireRow.Hidden = True
        Case "9:12"
            Rows("164:208").EntireRow.Hidden = True
            Rows("217:234").EntireRow.Hidden = True
         Case "10:12"
            Rows("164:217").EntireRow.Hidden = True
            Rows("226:234").EntireRow.Hidden = True
        Case "12:12"
            Rows("164:226").EntireRow.Hidden = True
    End Select

End Sub

Note that I made the simplifications I mentioned at the end of my previous thread (you may not have seen those, as I went back and edited the post and first posting it).

 

[billfinn]

So if the value of cell F31 is 4:12 and the user changes it to 5:12 I don't need to un-hide the detail rows for a 5:12 value in F31?
I appreciate your help very much.

 

[Joe4]

So if the value of cell F31 is 4:12 and the user changes it to 5:12 I don't need to un-hide the detail rows for a 5:12 value in F31?

No, because you already have this row at the top of your code.

    Rows.EntireRow.Hidden = False

That says EVERY time F31 is changed, first unhide EVERY row on your sheet.
So, when it enters the CASE statement, everything is already unhidden to start. So there is no need to do it again.

 

[billfinn]

Joe4,
Thanks very much! I had an existing macro named worksheet_change so I pasted the contents of your changes into that macro and it works perfectly.
I really appreciate it!

 

[Joe4]

Thanks very much! I had an existing macro named worksheet_change so I pasted the contents of your changes into that macro and it works perfectly.
I really appreciate it!

You are welcome!

Yes, you cannot have multiple procedures with the same name in the same module.

Note that you will need to be careful though, on how you combine the code. If you are checking for two very different things, you have to make sure that you don't accidentally run the other part unintentionally. If you need help with that, post the other code here too.