Good morning all,

Is there any way of identifying the type of a worksheet change.
I have such an event macro in a database that sets validation on cells in a
column, based on the value of the cell in the column to the left.
This works fine, but if I delete a row, .target doesn't have anything to act
on.
I need to be able to tell Excel to run the event macro if a change has been
made to a cell, but not to the structure of the workbook itself.

Is there any way in which I can do this?

I tried a condition with .target.cells.count, but this doesn't seem to be
quite what I need.

Thanks in advance

Pete

Well, sounds like at least for the condition provided, (deletion of a row)
that you need to know how many rows contain data before and after the
deletion. If there is a difference in that number, then you don't want to
perform the actions of the macro. As for other structural changes, you would
still have the concern of moving a cell from one location to another, if a
cell containing say data A is moved to a cell that must contain data of
type/value B, then there would be a problem and you would most likely want
your macro run. One problem with this aspect, is that if you have determined
that number of rows, and this piece of data was the only item in the last
row, then by the comparison of number of rows, the validation would not be
run. *smirk* So definetly a trade off.



"Peter Rooney" wrote:

Good morning all,

Is there any way of identifying the type of a worksheet change.
I have such an event macro in a database that sets validation on cells in a
column, based on the value of the cell in the column to the left.
This works fine, but if I delete a row, .target doesn't have anything to act
on.
I need to be able to tell Excel to run the event macro if a change has been
made to a cell, but not to the structure of the workbook itself.

Is there any way in which I can do this?

I tried a condition with .target.cells.count, but this doesn't seem to be
quite what I need.

Thanks in advance

Pete

Thanks for your thoughts.
I was originally getting round the problem by running a FOR loop on all the
values in the column, but, although it worked and it wasn't taking too long,
it didn't seem to be the best way to go about things.
I was obviously wrong! :-)

Regards

Pete



"GB" wrote:

Well, sounds like at least for the condition provided, (deletion of a row)
that you need to know how many rows contain data before and after the
deletion. If there is a difference in that number, then you don't want to
perform the actions of the macro. As for other structural changes, you would
still have the concern of moving a cell from one location to another, if a
cell containing say data A is moved to a cell that must contain data of
type/value B, then there would be a problem and you would most likely want
your macro run. One problem with this aspect, is that if you have determined
that number of rows, and this piece of data was the only item in the last
row, then by the comparison of number of rows, the validation would not be
run. *smirk* So definetly a trade off.



"Peter Rooney" wrote:

Good morning all,

Is there any way of identifying the type of a worksheet change.
I have such an event macro in a database that sets validation on cells in a
column, based on the value of the cell in the column to the left.
This works fine, but if I delete a row, .target doesn't have anything to act
on.
I need to be able to tell Excel to run the event macro if a change has been
made to a cell, but not to the structure of the workbook itself.

Is there any way in which I can do this?

I tried a condition with .target.cells.count, but this doesn't seem to be
quite what I need.

Thanks in advance

Pete

the .target will not refer to anything when the row, cell or column has
been deleted. This is your indication that the structure has changed.

to test for this you can use
if .target is nothing then
'row/col deleted
else
'do your stuff
end if


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=501980

There is another way that is "quicker" from what I gather... It uses the
xLUp function. If you search either with my user name (GB) or xlup, you
should see a recent example of something that returns the last row of column
using built in Excel commands (also quoted in here as being faster than a for
loop.) But we are talking about micro seconds provided you are not using all
65 thousand + rows.

"Peter Rooney" wrote:

Thanks for your thoughts.
I was originally getting round the problem by running a FOR loop on all the
values in the column, but, although it worked and it wasn't taking too long,
it didn't seem to be the best way to go about things.
I was obviously wrong! :-)

Regards

Pete



"GB" wrote:

Well, sounds like at least for the condition provided, (deletion of a row)
that you need to know how many rows contain data before and after the
deletion. If there is a difference in that number, then you don't want to
perform the actions of the macro. As for other structural changes, you would
still have the concern of moving a cell from one location to another, if a
cell containing say data A is moved to a cell that must contain data of
type/value B, then there would be a problem and you would most likely want
your macro run. One problem with this aspect, is that if you have determined
that number of rows, and this piece of data was the only item in the last
row, then by the comparison of number of rows, the validation would not be
run. *smirk* So definetly a trade off.



"Peter Rooney" wrote:

Good morning all,

Is there any way of identifying the type of a worksheet change.
I have such an event macro in a database that sets validation on cells in a
column, based on the value of the cell in the column to the left.
This works fine, but if I delete a row, .target doesn't have anything to act
on.
I need to be able to tell Excel to run the event macro if a change has been
made to a cell, but not to the structure of the workbook itself.

Is there any way in which I can do this?

I tried a condition with .target.cells.count, but this doesn't seem to be
quite what I need.

Thanks in advance

Pete

Hi, GB

I already have my range defined, using .currentregion, but I'll take a look
at your code example anyway.
Thanks a lot for your help

Pete



"GB" wrote:

There is another way that is "quicker" from what I gather... It uses the
xLUp function. If you search either with my user name (GB) or xlup, you
should see a recent example of something that returns the last row of column
using built in Excel commands (also quoted in here as being faster than a for
loop.) But we are talking about micro seconds provided you are not using all
65 thousand + rows.

"Peter Rooney" wrote:

Thanks for your thoughts.
I was originally getting round the problem by running a FOR loop on all the
values in the column, but, although it worked and it wasn't taking too long,
it didn't seem to be the best way to go about things.
I was obviously wrong! :-)

Regards

Pete



"GB" wrote:

Well, sounds like at least for the condition provided, (deletion of a row)
that you need to know how many rows contain data before and after the
deletion. If there is a difference in that number, then you don't want to
perform the actions of the macro. As for other structural changes, you would
still have the concern of moving a cell from one location to another, if a
cell containing say data A is moved to a cell that must contain data of
type/value B, then there would be a problem and you would most likely want
your macro run. One problem with this aspect, is that if you have determined
that number of rows, and this piece of data was the only item in the last
row, then by the comparison of number of rows, the validation would not be
run. *smirk* So definetly a trade off.



"Peter Rooney" wrote:

Good morning all,

Is there any way of identifying the type of a worksheet change.
I have such an event macro in a database that sets validation on cells in a
column, based on the value of the cell in the column to the left.
This works fine, but if I delete a row, .target doesn't have anything to act
on.
I need to be able to tell Excel to run the event macro if a change has been
made to a cell, but not to the structure of the workbook itself.

Is there any way in which I can do this?

I tried a condition with .target.cells.count, but this doesn't seem to be
quite what I need.

Thanks in advance

Pete

Tony,

This is definitely putting me on the right track, although there's still
some way to go.

Thanks a lot for your help

Pete



"tony h" wrote:


the .target will not refer to anything when the row, cell or column has
been deleted. This is your indication that the structure has changed.

to test for this you can use
if .target is nothing then
'row/col deleted
else
'do your stuff
end if


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=501980