hdtech96
05-11-2011, 11:05 AM
I have a simple routine built that hides some rows based on the value of a cell. Note the code below.
(I should add that I am not a programmer so this is code I have kluged together from other examples)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$U$13" Then
If Target.Value = 1 Then
Rows("65:77").EntireRow.Hidden = True
Else
If Target.Value = 2 Then
Rows("65:77").EntireRow.Hidden = False
End If
End If
End If
End Sub
My problem comes with the cell U13 itself. If I manually enter 1 or 2 into that cell, everything works great. If I change U13 to be a formula (IF) that results only in 1 or 2, the code no longer works. I am not sure if it matters but the IF formula results are dependent on results from another sheet.
I was worried that it was a text vs number kind of thing so I added quotes around the 1 or 2 in my code and that didn't matter. I tried reading U13 into string variable and going about that way but still no luck.
I now understand that Worksheet_Change is not what I want because a formula update is not considered a change. I think I need worksheet_calculate? So I made this version of code:
Private Sub Worksheet_Calculate()
Dim MyResult As String
Application.EnableEvents = False
Rows("1:" & Worksheets("Case").UsedRange.Rows.Count).EntireRow.Hidden = False
MyResult = Worksheets("Case").Cells(21, 13).Value
Select Case MyResult
Case "1"
Rows("65:77").EntireRow.Hidden = True
Case "2"
Rows("65:77").EntireRow.Hidden = False
End Select
Application.EnableEvents = True
End Sub
This doesnt appear to work either! :dunno
I have attached a trimmed down version of the file. Note that there are many more tabs so while S13 can be changed in this version, S13 is linked to another sheet in the real version. I just made U13 as a placeholder to reference.
Thanks for any help!
(I should add that I am not a programmer so this is code I have kluged together from other examples)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$U$13" Then
If Target.Value = 1 Then
Rows("65:77").EntireRow.Hidden = True
Else
If Target.Value = 2 Then
Rows("65:77").EntireRow.Hidden = False
End If
End If
End If
End Sub
My problem comes with the cell U13 itself. If I manually enter 1 or 2 into that cell, everything works great. If I change U13 to be a formula (IF) that results only in 1 or 2, the code no longer works. I am not sure if it matters but the IF formula results are dependent on results from another sheet.
I was worried that it was a text vs number kind of thing so I added quotes around the 1 or 2 in my code and that didn't matter. I tried reading U13 into string variable and going about that way but still no luck.
I now understand that Worksheet_Change is not what I want because a formula update is not considered a change. I think I need worksheet_calculate? So I made this version of code:
Private Sub Worksheet_Calculate()
Dim MyResult As String
Application.EnableEvents = False
Rows("1:" & Worksheets("Case").UsedRange.Rows.Count).EntireRow.Hidden = False
MyResult = Worksheets("Case").Cells(21, 13).Value
Select Case MyResult
Case "1"
Rows("65:77").EntireRow.Hidden = True
Case "2"
Rows("65:77").EntireRow.Hidden = False
End Select
Application.EnableEvents = True
End Sub
This doesnt appear to work either! :dunno
I have attached a trimmed down version of the file. Note that there are many more tabs so while S13 can be changed in this version, S13 is linked to another sheet in the real version. I just made U13 as a placeholder to reference.
Thanks for any help!