Can't get Worksheet_Calculate to fire

bhmohr

New Member
Joined
Jul 19, 2011
Messages
2
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Hi
Welcome to the board

The event procedure interface is wrong, it should be:

Code:
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top