Identifying the type of a Worksheet_Change

[Peter Rooney]

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

[GB]

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
>

[Peter Rooney]

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 h]

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

[GB]

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
> > >

[Peter Rooney]

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
> > > >

[Peter Rooney]

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
>
>