Can't get Worksheet_Calculate to fire

[bhmohr]

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

 

[pgc01]

Hi
Welcome to the board

The event procedure interface is wrong, it should be:

Private Sub Worksheet_Calculate()

without any parameter.

Please test.


Anyway, In my opinion you should only use the Calculate event procedure as a last resource, it's inefficient since it's fired no matter what you change in the worksheet.

In your case, if I understand correctly, the value in C10 is changed manually so I would use the Change event procedure, check if it was the cell C10 that changed, and, if true, test D10 and perform the actions.

 

[bhmohr]

Nope.
After removing the argument to simply read as ...
Sub Worksheet_Calculate()

...(leaving the remaining code the same) and saving, when I changed the value of C10, no rows were either hidden or unhidden (depending on the value I changed it to such that D10 was + or -).

Any other troubleshooting suggestions?

--Brian