Worksheet_Change with Named Ranges not Responding

[Arty Choke]

I created a worksheet with the following code to cause a macro to execute when the values in certain cells change:


Private Sub Worksheet_Change(ByVal Target As Range)
'Check to see that we have enabled auto macro updating
If Range("Report_Type_Auto_Run_Macros") = "ENABLED" Then
'Remember which cell I started on, so I can return there
Starting_Cell = ActiveCell.Address
'Turn off ALL events so the sub does not put the code into a loop.
Application.EnableEvents = False
'Hide and Unhide Rows - Did we change something warranting a Row Hide / Unhide update?
'******
If Not Intersect(Target, Range("Report_Type_Row_Flags", "Report_Type_Selected")) Is Nothing Then
Call Hide_and_Unhide_Rows_and_Columns
End If
'Turn on ALL events
Application.EnableEvents = True
'Return to the cell I started on
Range(Starting_Cell).Select
End If
End Sub

I noticed that this macro responded to more cells than I intended (cells outside of the named ranges). I determined that I had made an error in th e line following "******". I had enclosed each named ranged in its own pair of quote marks. They should have both been grouped within a single pair of quote marks. I changed that line as follows:

If Not Intersect(Target, Range("Report_Type_Row_Flags, Report_Type_Selected")) Is Nothing Then

The code now only responds to changes in the named ranges, however, it also only responds when I physically enter new data into the cell. It no longer responds when the formulas in those cells cause the values to change. (It used to respond to either type of change, in the original version.)

How do I cause a Worksheet_Change event to react when the value in a cell changes as a result of a formula?

Thanks!
Arty

 

[lenze]

If Not Intersect(Target, Union(Range("Report_Type_Row_Flags, Report_Type_Selected"))) Is Nothing Then

If your cell changed via formula, you will need the WorkSheet_Calculate Event, but try to avoid that if you can. Sometimes it's not easy to work with. You may be able to trigger the code off of a "precedent" instead.

lenze

By the way, we used to have a "parsnips" on the board, but he got steamed one night and changed his name (initials RS)

 

[Arty Choke]

Thanks! But a few more problems...

1) I copied the change you suggested, and upon execution received: "Compile Error: Argument not optional". Union was highlighted.

2) Assuming I can get the Union version to work, is there some benefit in using it as opposed to just putting the named ranges within a single pair of quotes?

3) Now I know why it worked in the last version... I was using a drop down box that was probably in the range of monitored cells... It was just that my range of monitored cells was bigger than intended...

4) I really need the ability to execute the macro based on value changes caused by formulas. What do I need to learn to make proper use of WorkSheet_Calculate Event?

5) I did not understand your suggestion: "trigger the code off of a "precedent" instead". What does that mean? Does that mean to base the event on whatever data input is causing the formula to compute the new value (my drop down box)?

6) Is there a place that I can look at all of the commands and functions with explanation of their syntax? I have searched and not found.

7) I in advertantly double posted... the first time I wrote up my question, the browser did not respond, and I looked for and did not find my post. So I re-typed it all, and now have two. Is there a way to delete the first? Should I reply to it and explain that it is a duplicate?


THANK YOU!!!!
Arty

 

[lenze]

Sorry, I didn't change it all the way. Should be

If Not Intersect(Target, Union(Range("Report_Type_Row_Flags") Range("Report_Type_Selected"))) Is Nothing Then

lenze

 

[Arty Choke]

Thanks again Lenze. You are still missing a comma in that version between the two Range(), but I got it working.

In my solution I also including cells that can cause the cells that contain formulas to change. Not elegant, but it will work.


Any comments on the remianing questions?

2) Assuming I can get the Union version to work, is there some benefit in using it as opposed to just putting the named ranges within a single pair of quotes?

4) I really need the ability to execute the macro based on value changes caused by formulas. What do I need to learn to make proper use of WorkSheet_Calculate Event?

6) Is there a place that I can look at all of the commands and functions with explanation of their syntax? I have searched and not found.

7) I in advertantly double posted... the first time I wrote up my question, the browser did not respond, and I looked for and did not find my post. So I re-typed it all, and now have two. Is there a way to delete the first? Should I reply to it and explain that it is a duplicate?

Thanks again!
Arty

 

[lenze]

Sorry about the comma!!!
I don't know what you mean. From your example, I believe you will need the union. Maybe you should post an example of what you mean by "named ranges within a single pair of quotes"?
The Calculate event, unfortunately does not set a Target range, so it is tricky to work with. Perhaps a detailed explanation with cell addresses and formulas would help!
For a resource, you can check out books on VBA. Your public library should have some, or you can check out the MrExcel Store. The VBA Help file and prompt window can also be useful.
As for your duplicate post, you have a short window to "Delete" it. You can, however, report it (Upper right corner of the post) and ask the moderators to remove it.

lenze