Consulting

Results 1 to 8 of 8

Thread: detect changed cell in a column

  1. #1

    detect changed cell in a column

    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):
    [vba]
    Function copiereNrTura(adresaTura As Range)
    copiereNrTura = adresaTura.Value
    End Function
    [/vba]
    How I will trigger the canged cell to run the procedure to write in (A17:AI17, A42:AI42,...)?

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    A simplified example
    [vba] 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
    [/vba]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    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.
    [VBA]

    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

    [/VBA]

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

  4. #4
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    The basic code is OK, but you need to prevent the code from looping; changing X11 triggers the code

    Try
    [VBA]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
    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #5
    Still not working.. Just the message box appears but
    [VBA]Range("X11").Value = 4[/VBA] has no effect, the cell is empty.

  6. #6
    Hmmm , I read it now somewhere to an article on the internet that my file to be corrupted...

  7. #7
    I copied in a new workbook but same problem...

  8. #8
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    I've tested this in 2003 and 2007. Can you post your workbook?
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •