Sub Worksheet_Change(ByVal Target As Excel.Range) Questions
 

I'm creating a form and I'm using a macro to ensure various parts of
the form are entered accurately. So when the user changes a cell value
I make sure it's valid and I also make sure the cell format options
aren't changed like the font size etc etc..

However I have a portion of the form from row 12 and up where the user
has pretty much unlimited space to work with. I do validate the data
they enter but I also want them to have the freedom to erase entire
rows at this point. So when they do erase a row(s) my application
attempts to validate the data and updates the formats of these cells.
Now the contents of the cell DOES get removed correctly but the
actually row stays as a blank one. So when the user goes to print or
even save the next time they open the sheet it still considers those
rows to exist.

Is there a way I can test the method of change to a cell ie if the user
deleted it from the sheet vs changed the value/format options etc etc?
So I can abort validating the data if the user is just erasing
cells/rows?

Sub Worksheet_Change(ByVal Target As Excel.Range) Questions
 

Is Sub Worksheet_Change(ByVal Target As Excel.Range) an Excel library
function?
Where can I find information on it?

With regard to your Q:
take a look at the posting "how to overwrite a value without loosing the
formula?"
in microsoft.public.excel.worksheet.functions ng posted on 7/9/05,
and specifically at the reply by JE McGimpsey
It might be of help.

Sorry could not help more,
/mark

Wescotte wrote:

I'm creating a form and I'm using a macro to ensure various parts of
the form are entered accurately. So when the user changes a cell value
I make sure it's valid and I also make sure the cell format options
aren't changed like the font size etc etc..

However I have a portion of the form from row 12 and up where the user
has pretty much unlimited space to work with. I do validate the data
they enter but I also want them to have the freedom to erase entire
rows at this point. So when they do erase a row(s) my application
attempts to validate the data and updates the formats of these cells.
Now the contents of the cell DOES get removed correctly but the
actually row stays as a blank one. So when the user goes to print or
even save the next time they open the sheet it still considers those
rows to exist.

Is there a way I can test the method of change to a cell ie if the user
deleted it from the sheet vs changed the value/format options etc etc?
So I can abort validating the data if the user is just erasing
cells/rows?

Sub Worksheet_Change(ByVal Target As Excel.Range) Questions
 

Hi Mark,

It is a Worksheet event procedure.

For iinformation on event procedures, see Cgip Pearson at:

http://www.cpearson.com/excel/events.htm


---
Regards,
Norman



"Mark Dvorkin" wrote in message
...
Is Sub Worksheet_Change(ByVal Target As Excel.Range) an Excel library
function?
Where can I find information on it?

With regard to your Q:
take a look at the posting "how to overwrite a value without loosing the
formula?"
in microsoft.public.excel.worksheet.functions ng posted on 7/9/05,
and specifically at the reply by JE McGimpsey
It might be of help.

Sorry could not help more,
/mark

Wescotte wrote:

I'm creating a form and I'm using a macro to ensure various parts of
the form are entered accurately. So when the user changes a cell value
I make sure it's valid and I also make sure the cell format options
aren't changed like the font size etc etc..

However I have a portion of the form from row 12 and up where the user
has pretty much unlimited space to work with. I do validate the data
they enter but I also want them to have the freedom to erase entire
rows at this point. So when they do erase a row(s) my application
attempts to validate the data and updates the formats of these cells.
Now the contents of the cell DOES get removed correctly but the
actually row stays as a blank one. So when the user goes to print or
even save the next time they open the sheet it still considers those
rows to exist.

Is there a way I can test the method of change to a cell ie if the user
deleted it from the sheet vs changed the value/format options etc etc?
So I can abort validating the data if the user is just erasing
cells/rows?

Sub Worksheet_Change(ByVal Target As Excel.Range) Questions
 

Sub Worksheet_Change(ByVal Target As Excel.Range) is an Excel lib
function and it is called after the user changes the value/format of
any cell.

I took a glance at the post you described and it's not related to what
I need to do. Let me try to explain it more clearly.

Say I have 100 rows. Now I delete 10 from the middle and the total rows
drops to 90 and the rows are labeled 1 to 90.

However in my case I delete the 10 rows and the row count stays at 100.
Because the function Sub Worksheet_Change() is called and I examine the
user input which turns out to be making the cell values empty.

Now I can't simply say delete a row if the contents are empty because I
need to allow the user to have empty cells/rows. I just want a way to
determine if the user has actually removed a row/cell instead of just
emptying the contents.

For example if you highlight a row and hit the delete key it just
empties the contents. But if you right click on the row and select
delete it actually removes the row from teh sheet and subtracts 1 from
the total row count.

Does this make more sense?