Compare named ranges
 

I have a few 50x10 ranges which contain alphanumeric comments added by
the user. At init, some comments may be programmatically put in those
ranges and the user is free to add/edit/clear the ranges.

Is there a way to detect which ranges have changed? Exactly what cell
has changed or what value has changed is not important; identifying
that a change has occurred or that a comment has been cleared will
suffice.

I can store the initial comments in a duplicate range some place and
iterate cell-by-cell in VBA to compare but this may not be the best
way, so thought I'd ask. I'm assuming if a range is cleared, CountA
probably is the quickest way to detect this.

Thanks

Compare named ranges
 

Hi,

you may use:

Private Sub Worksheet_Change(ByVal Target As Range)

Target is the range that have changed, see help for more details

Regards,
Ivan

Compare named ranges
 

Thanks, but unfortunately, it might not work properly as there's tons
of existing code in the app which runs with enableEvents = false. This
is why I thought of pushing the comparison to the very end; maybe at
workbook_close, or in this case, the save (since some info is getting
pushed to a db).

The workflow is, if it helps:
1) Open
1a) Some data pushed into the ranges marked for users' comments.
2) User modifies the file, including possibly the comments ranges
3) User finishes work with file and marks it to be closed/saved
3a) Detect whether a comment was changed or cleared (or added, if
possible)
3b) Save and close

Thanks
-- Dev

Compare named ranges
 

Thanks, but unfortunately, it might not work properly as there's tons
of existing code in the app which runs with enableEvents = false. This
is why I thought of pushing the comparison to the very end; maybe at
workbook_close, or in this case, the save (since some info is getting
pushed to a db).

The workflow is, if it helps:
1) Open
1a) Some data pushed into the ranges marked for users' comments.
2) User modifies the file, including possibly the comments ranges
3) User finishes work with file and marks it to be closed/saved
3a) Detect whether a comment was changed or cleared (or added, if
possible)
3b) Save and close

Thanks

Compare named ranges
 

Hi,

how will workbook_close work with application.enableevents = false?

Maybe a clear solution is to go through your code and make sure that
you disable events when you need them disabled and enable them
immediately when possible. Otherwise you may experience "strange"
behaviour with events not firing when they should.

The benefit from this for you is much clearer code and that you can use
worksheet_change event.

Regards,
Ivan

Compare named ranges
 

Agreed and thanks... I was unclear in that the Workbook_Close is
user-initiated rather than code-initiated.

There's a lot of calculation specific code in the workbook and this is
where events are disabled/enabled. Therefore, it's not possible for me
(since I don't own the calc part of the workbook) to ensure that I
check the comments area after each calc output.

Compare named ranges
 

Hi,

so then probably the way you indicated in your first post is the most
convenient way how to perform the check for changed values (comments).

Regards,
Ivan