Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Private Sub Worksheet_Change(ByVal Target As Excel.Range) | Excel Programming | |||
Private Sub Worksheet_Change(ByVal Target As Excel.Range) | Excel Worksheet Functions | |||
Sub Worksheet_Change(ByVal Target As Excel.Range) Questions | Excel Programming | |||
Worksheet_Change(ByVal Target As Excel.Range) | Excel Programming | |||
Excel VBA .... Worksheet_Change(ByVal Target As Range) question | Excel Programming |