Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace all the changed cells range...
Hi Can any body help me how to idenify the changed range of cells. If cells D1:D4 are selected and deleted then cells E1:E4 should also be deleted. I am using the following code for it. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) changed_row = Target.Row changed_column = Target.Column ' Do the process End Sub But with the above code, I am able to trace only first cell D1 and I am deleting E1. How to put it in loop to trace all the changed cells range. Thanks in advance Sridhar P -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace all the changed cells range...
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
if Target.Column.count = 1 and Target.Column = 1 then Application.EnableEvents = False Target.offset(0,1).ClearContents End if Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Sridhar Pentlavalli via OfficeKB.com" wrote in message ... Hi Can any body help me how to idenify the changed range of cells. If cells D1:D4 are selected and deleted then cells E1:E4 should also be deleted. I am using the following code for it. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) changed_row = Target.Row changed_column = Target.Column ' Do the process End Sub But with the above code, I am able to trace only first cell D1 and I am deleting E1. How to put it in loop to trace all the changed cells range. Thanks in advance Sridhar P -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace all the changed cells range...
Hi Tom,
Thank you for the help. I am getting the error "Compiler Error" "Invalid Qualifier" and "Column" is highlited in the expression "Targer.Column.Count"... Can you help me .... -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace all the changed cells range...
if Target.Column.count = 1 and Target.Column = 1 then
should be if Target.Columns.count = 1 and Target.Column = 1 then make the first column plural (columns) -- Regards, Tom Ogilvy "Sridhar Pentlavalli via OfficeKB.com" wrote in message ... Hi Tom, Thank you for the help. I am getting the error "Compiler Error" "Invalid Qualifier" and "Column" is highlited in the expression "Targer.Column.Count"... Can you help me .... -- Message posted via http://www.officekb.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace all the changed cells range...
Hi Tom
That is excallent.... It is working now... Can I trace the row numbers also....? Here is my exact requirement.... I will be having a sheet with 7 columns out of which first 5 columns are unprotected. 6th and 7th columns are protected. Now when ever user deletes any of the value in row I, i should blank the cell (I, 6) and put "Y" in cell (I, 7). If user deletes one value in one cell ... then its ok.... I can manage .... but the intelligent user is selecting say, from (2,4) to (6,4) and deleting. Then for all the rows from 2 to 6, 6th column should be emptyed out and 7th column should have "Y". Can you please help.......? -- Message posted via http://www.officekb.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace all the changed cells range...
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rng as Range Application.EnableEvents = False ' check if cells have been cleared if application.CountA(Target) = 0 then ' Activesheet.Unprotect Password:="ABCD" set rng = Intersect(Target.EntireRow,Columns(6)) rng.ClearContents rng.offset(0,1).Value = "Y" 'Activesheet.Protect Password:="ABCD" end if Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Sridhar Pentlavalli via OfficeKB.com" wrote in message ... Hi Tom That is excallent.... It is working now... Can I trace the row numbers also....? Here is my exact requirement.... I will be having a sheet with 7 columns out of which first 5 columns are unprotected. 6th and 7th columns are protected. Now when ever user deletes any of the value in row I, i should blank the cell (I, 6) and put "Y" in cell (I, 7). If user deletes one value in one cell ... then its ok.... I can manage .... but the intelligent user is selecting say, from (2,4) to (6,4) and deleting. Then for all the rows from 2 to 6, 6th column should be emptyed out and 7th column should have "Y". Can you please help.......? -- Message posted via http://www.officekb.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace all the changed cells range...
Hi Tom
Lots of thanx for the help.... It worked and now my application became very flexible and all those (my) client appriciations goes to you....:-) I have an another problem in my project which I posted on 29 Dec 2004 in the same forum and eagerly waited for any help.... but unfortunately no reply ... Can you just have a look to help me... Here is the link to the problem... http://www.officekb.com/Uwe/Forum.as...OfficeKB.c om -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
trace dependent tool doesn't work but trace precendent doesn't | Excel Discussion (Misc queries) | |||
Trace Precedents and dependents for a range of cell ? | New Users to Excel | |||
Lock cells, but still be able to trace | Excel Worksheet Functions | |||
Trace Dependents for range of cells | Excel Discussion (Misc queries) | |||
how do trace dependents for all cells at once? | Excel Discussion (Misc queries) |