How to trap delete row event and hide column event?

[Alan]

Hi All,

I have a need to trap two events that I cannot see how to achieve in
Excel 2000.

1) Delete (entire) row event.

This does trigger the worksheet_change event but I need to return the
specific row (or rows) that were deleted and know that it was deleted
(rather than just changed).

The target value will tell me the cell (and hence row) that was
changed, but how do I know whether it was actually deleted or just
changed?

Of course, after being deleted, the row still exists, albeit a new row
that was previous below the deleted row.



2) How can I identify when a column (or range of columns) has been
hidden or unhidden?

This does not seem to trigger the worksheet_change event or any other
that I can determine?


Thanks,

Alan.

 

[Vasant Nanavati]

Sadly, you can't trap the deletion or the hiding/unhiding of a row or
column.

 

[Alan]

Sadly, you can't trap the deletion or the hiding/unhiding of a row
or column.

Hi Vasant,

Thought as much.

I am thinking perhaps I could use something like an 'ontime' macro
that generates an array of column widths every 5 seconds (say) and
compares to the previous array. A change would be then act as my
event catcher.

Do you think that has legs? If so, I will give it a go.

Thanks for your input.

Alan.


PS: What about the deleting event? Any thoughts on that one?

 

[Bob Phillips]

Here is a technique that Rob Bovey posted some time ago. It is a kludge as
Rob says, but it may work for you

There's no direct way to do this. I created a workaround for one project
where I needed to do this, but it was very clunky. As a rule, I always hide
the first row and column of worksheets in my projects. This gives me scratch
space to work in and is required for this solution to work (at least the
hidden first column is required in this case).

In my hidden first column I placed the number 1 in every cell of the
area the user might interact with (let's say A1:A100). Then in cell A101 I
placed the formula

=COUNTBLANK(A1:A100)

and gave it the range name "CheckInsert". In cell A102 I placed the *array
formula* (entered with Ctrl+Shift+Enter)

=MATCH(TRUE,ISBLANK(A1:A100),0­)

and gave it the range name "FindInsert".

Each time the worksheet_calculate event fired, I would check the
CheckInsert cell. If it contained any number other than zero, I would know
the user had inserted a row, and how many they had inserted. The FindInsert
range would then contain the number of the row where the insert began.

I would then turn off calculation, take the action I needed to take, add
1s into the just-inserted rows, and turn calculation back on. You can extend
this general method to include finding deletions by using sequentially
numbered cells in the first column, rather than 1s.

--
Rob Bovey, MCSE
The Payne Consulting Group
http://www.payneconsulting.com