Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting when a user deletes a row
I have a script working using
Private Sub Worksheet_Change(ByVal Target As Excel.Range) so I can format various cells after the user has entered data... However when a user deletes an entire row the Worksheet_Change() and since no value exist in each cell in that row (because it was just deleted) my application resets to the default values. Basically repopulating the row so it can never actually be deleted. Now, my default values are pretty much "" so the row appears to be empty to the user however say I had 500 rows and I erased 400 on the bottom. If I go to print it will spit out quite a few more empty pages. Now, one solution is to simply not allow for empty rows during the printing process just erase them. But I really want the user to have the ability to insert their own whitespace to make things easier to read. Really what I believe I need to do is figure out how detect when the user is doing a right click on a row and clicking delete. Instead of calling Worksheet_Change() perform the actual removal of the row. Any idea how to do this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting when a user deletes a row
If the issue is the print area being bad you might try resetting it in the
before print event to the range with data: Private Sub Workbook_BeforePrint(Cancel As Boolean) Range("A1", Cells(Cells.Find("*", Range("A1"), , , _ xlByRows, xlPrevious).Row, Cells.Find( _ "*", Range("A1"), , , xlByColumns, xlPrevious) _ .Column)).Name = "Print_area" End Sub This code goes in the ThisWorkbook module. As it is now it will fire for any worksheet in the workbook. If that's an issue you could add code to check that the right sheet is active. -- Jim "Wescotte" wrote in message ups.com... |I have a script working using | | Private Sub Worksheet_Change(ByVal Target As Excel.Range) | | so I can format various cells after the user has entered data... | | However when a user deletes an entire row the Worksheet_Change() and | since no value exist in each cell in that row (because it was just | deleted) my application resets to the default values. Basically | repopulating the row so it can never actually be deleted. | | Now, my default values are pretty much "" so the row appears to be | empty to the user however say I had 500 rows and I erased 400 on the | bottom. If I go to print it will spit out quite a few more empty pages. | | | Now, one solution is to simply not allow for empty rows during the | printing process just erase them. But I really want the user to have | the ability to insert their own whitespace to make things easier to | read. | | Really what I believe I need to do is figure out how detect when the | user is doing a right click on a row and clicking delete. Instead of | calling Worksheet_Change() perform the actual removal of the row. | | Any idea how to do this? | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting when a user deletes a row
Adding the check during print sort of works..
Yes, if they print it it will be fine however say a user decides to delete 100+ rows the system preforms a Worksheet_Change() for each cell.. It can lag up to 15 seconds to completely erase the rows because it's attempting to format each cell as it erases it. This is the same case for a paste.. In a paste I can accept the lag because there is no way around it but when I'm simply deleting rows there is no reason to attempt to format them correctly as I erase them. Really, I need to find a way to determine when an entire row is being removed from the sheet. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting when a user deletes a row
Did you ever find out how to do this?
Thanks, wjewell "Wescotte" wrote: Adding the check during print sort of works.. Yes, if they print it it will be fine however say a user decides to delete 100+ rows the system preforms a Worksheet_Change() for each cell.. It can lag up to 15 seconds to completely erase the rows because it's attempting to format each cell as it erases it. This is the same case for a paste.. In a paste I can accept the lag because there is no way around it but when I'm simply deleting rows there is no reason to attempt to format them correctly as I erase them. Really, I need to find a way to determine when an entire row is being removed from the sheet. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting when a user deletes a row
Nope...
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting when a user deletes a row
If anyone has some fresh insight on this issue, I too would like to know in “Worksheet_Change” if a row has been deleted by the user. My intuitive misconception looks like this: Private Sub Worksheet_Change(ByVal Target As Range) If Rows(Target.Row).Delete Then ‘ ‘ ‘ Thgank you -- Bird ------------------------------------------------------------------------ Bird's Profile: http://www.excelforum.com/member.php...o&userid=24469 View this thread: http://www.excelforum.com/showthread...hreadid=401222 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting when a user deletes a row
Does that solution actually work? I'm still looking for a way to detect
if a Change is actually a row being deleted. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting when a user deletes a row
No. I wish it did. Makes sense to me. but no it doesn't work -- Bir ----------------------------------------------------------------------- Bird's Profile: http://www.excelforum.com/member.php...fo&userid=2446 View this thread: http://www.excelforum.com/showthread.php?threadid=40122 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Detecting when a user deletes a row | Excel Worksheet Functions | |||
Detecting user idle time | Excel Programming | |||
Detecting when user deselects an add-in | Excel Programming | |||
Detecting Ctrl-Tabs in User Form input fields? | Excel Programming | |||
Detecting Input Change on User Form | Excel Programming |