PDA

View Full Version : detect changed cell in a column



leroiv
02-04-2009, 04:32 AM
Hi everyone!
A have a worksheet with the following data:

AZ5=4 , BA5=3, BB5=2, BC5=1

in column AP , I have a function in specific cells who copies the values from above cells:

AP42=3, AP47=3, AP52=2, AP57=1, AP60=1 and so on.. (about 100 cells copies data from cells above)
What I want: when I change values in AZ5,BA5,BB5,BC5 the cells from column AP (AP42,AP47,AP52,AP57,AP60... with the function "copiereNrTura" in it) to copy values, and changed cell (AP42,AP47,AP52,AP57,AP60..) to trigger another procedure who will write something in another cells (A42:AI42,A47:AI47,A52:AI52,A57:AI57,A60:AI60..). How I will do that??
Many thanks for your replies!

What I have:
(this is the function who copies values form AZ5,BA5,BB5,BC5):

Function copiereNrTura(adresaTura As Range)
copiereNrTura = adresaTura.Value
End Function

How I will trigger the canged cell to run the procedure to write in (A17:AI17, A42:AI42,...)?

mdmackillop
02-04-2009, 11:53 AM
A simplified example
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Select Case Target.Address(0, 0)
Case "A1", "A2", "A3"
Macro1 Target
Case "A4", "A5", "A6"
Macro2 Target
Case Else
Macro3
End Select
Application.EnableEvents = True
End Sub

Sub Macro1(Target As Range)
Target.Offset(, 3) = Target
End Sub

Sub Macro2(Target As Range)
Target.Offset(, 5) = Target
End Sub

Sub Macro3()
MsgBox "Nothing to do"
End Sub

leroiv
02-14-2009, 12:42 PM
Thank you for reply
Another thing I want to do :
In a cell I have a "data validation list" (R11) . When I select an item from the drop-down list of DV (data validation) I want to trigger a subrutine to write a value in another cell (X11) but it isn't work. Only thing that is working is the message box that appears but the range X11 remains empty.


Private Sub Worksheet_Change(ByVal target As Range)
Select Case target.Address(False, False)
Case "W11"
Range("Y11").Value = 8
Case "R11"
MsgBox "It's not working!"
Range("X11").Value = 4
'Intersect(target.EntireRow, Cells(11, "X")).Value = 4
'target.Offset(0, 6)=4
End Select
End Sub



The italic lines are commented and are another option I have tried but still not working. I'm using Excel 2002

mdmackillop
02-14-2009, 12:59 PM
The basic code is OK, but you need to prevent the code from looping; changing X11 triggers the code

Try
Private Sub Worksheet_Change(ByVal target As Range)

On Error GoTo Exits
Application.EnableEvents = False
Select Case target.Address(False, False)

Case "W11"
Range("Y11").Value = 8
Case "R11"
MsgBox "It's not working!"
Range("X11").Value = 4
'Intersect(target.EntireRow, Cells(11, "X")).Value = 4
'target.Offset(0, 6)=4
End Select
Exits:
Application.EnableEvents = True
End Sub

leroiv
02-14-2009, 01:13 PM
Still not working.. Just the message box appears but
Range("X11").Value = 4 has no effect, the cell is empty.

leroiv
02-14-2009, 01:39 PM
Hmmm , I read it now somewhere to an article on the internet that my file to be corrupted...

leroiv
02-14-2009, 02:31 PM
I copied in a new workbook but same problem... :(

mdmackillop
02-14-2009, 04:02 PM
I've tested this in 2003 and 2007. Can you post your workbook?