Noob here. Sorry to trouble y'all with such a tired and seemingly simple problem, but try as I may I simply CANNOT get this code to work in Excel 2003 - cobbling it together as I have from others' code on this board who've had similar (but not exact) objectives as mine.
My target cell (D10) is the result of a formula (B10 - C10). What I'd like to have happen is that based on whatever value the user manually enters in C10, the resulting value of D10 (whether positive or negative) should cause some unrelated rows to be hidden or shown.
I've tried the _Calculate event, and I've tried Worksheet_Change (although the user should never click on D10 to change it). Neither seems to work. If I omit the target argument, I can get it to fire manually by clicking on the "play" button in the VBA editor, but I'd really like it to hide or show the rows based on the resulting value of D10, NOT whether a button is clicked.
When I put a break point in the code, and change the value of C10, nothing happens - it seems to never hit the code. What am I missing here? Can this not be done?
Please tell me what I am doing wrong (It's undoubtedly something simple)
Thanks a bunch,
Brian
- - - - - - - - - - - - - - - - - - -
Sub Worksheet_Calculate(ByVal Target As Range)
Application.EnableEvents = False
If Target("D10").Value > 0 Then
ActiveSheet.Rows("11:19").Hidden = True
ActiveSheet.Rows("20:30").Hidden = False
ElseIf Target("D10").Value < 0 Then
ActiveSheet.Rows("20:30").Hidden = True
ActiveSheet.Rows("11:19").Hidden = False
End If
Application.EnableEvents = True
End Sub
My target cell (D10) is the result of a formula (B10 - C10). What I'd like to have happen is that based on whatever value the user manually enters in C10, the resulting value of D10 (whether positive or negative) should cause some unrelated rows to be hidden or shown.
I've tried the _Calculate event, and I've tried Worksheet_Change (although the user should never click on D10 to change it). Neither seems to work. If I omit the target argument, I can get it to fire manually by clicking on the "play" button in the VBA editor, but I'd really like it to hide or show the rows based on the resulting value of D10, NOT whether a button is clicked.
When I put a break point in the code, and change the value of C10, nothing happens - it seems to never hit the code. What am I missing here? Can this not be done?
Please tell me what I am doing wrong (It's undoubtedly something simple)
Thanks a bunch,
Brian
- - - - - - - - - - - - - - - - - - -
Sub Worksheet_Calculate(ByVal Target As Range)
Application.EnableEvents = False
If Target("D10").Value > 0 Then
ActiveSheet.Rows("11:19").Hidden = True
ActiveSheet.Rows("20:30").Hidden = False
ElseIf Target("D10").Value < 0 Then
ActiveSheet.Rows("20:30").Hidden = True
ActiveSheet.Rows("11:19").Hidden = False
End If
Application.EnableEvents = True
End Sub