Unhide depending on selection

[Jim Jones]

Hi,

I have a workbook which has a sheet where a user can select Yes or No to a
list of services. When they click on the next sheet if their response was
"Yes" then a specific range should be revealed (I'm using Outlining).

I have some OnActivate VBA on the sheet that has the rows than need to be
hidden etc which calls a macro to do this which basically runs down the list
of responses and checks for "Yes" or "No" and then hides or unhides the
relevant range (range names held in an adjacent column). The VBA that is
called is below:

Dim rCell As Range
Dim sSection As String
Dim iTableOffset As Integer
Dim iUserFlag As Integer

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'make sure that the user section is unhidden
iUserFlag = Application.WorksheetFunction.CountIf(Range("Towers"), "Yes")

If iUserFlag > 0 Then
Range("UsersRng").EntireRow.Hidden = False
Range("ServTakeRng").EntireRow.Hidden = False
Else
Range("UsersRng").EntireRow.Hidden = True
Range("ServTakeRng").EntireRow.Hidden = True
End If

' Get how many columns to offset
iTableOffset = Range("TableOffset").Value

For Each rCell In Range("Towers")
sSection = rCell.Offset(0, iTableOffset).Value
If rCell.Value = "Yes" Then
Range(sSection).EntireRow.Hidden = False
Else
Range(sSection).EntireRow.Hidden = True

End If
Next rCell

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


The problem is that this is randomly working and randomly not working and I
have no idea why, it is like Excel isn't recognising that something has
changed? Can any one help?