• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Detect cell value change and copy the new value to another cell

V

VB_Noob

Member
I have been trying to modify attached program so that when the value of cell "B12", "B13" changes, the program will then take the newly entered the value of "B12" and "B13" and paste it to cells located at the button of the sheet ("I22:J24").
 

Attachments

  • Custom_Filter_Update Record Testing.xlsm
    21.1 KB · Views: 7
Try this modification to your code
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$N$3" Then
  V = Application.Match(Target.Value, Range("A21", [A21].End(xlDown)), 0)

  If IsNumeric(V) Then
    V = 20 + V
    [B3].Value = Target.Value
    [D3].Value = Cells(V, 2).Value
    [F3].Value = Cells(V, 3).Value
    [H3].Value = Cells(V, 4).Value
    [B5].Value = Cells(V, 5).Value
    [B7].Value = Cells(V, 6).Value
    [B9].Value = Cells(V, 7).Value
    [B11].Value = Cells(V, 8).Value
    [B12].Value = Cells(V, 9).Value
    [B13].Value = Cells(V, 10).Value
    [B14].Value = Cells(V, 11).Value
  Else
    If Target.Value > "" Then Beep
      [B3,D3,F3,H3,B5,B7,B9,B11,B12.B13.B14].Value = ""
    End If

ElseIf Target.Address = "$B$13" Then

  Dim lr As Integer
  lr = Range("A" & Rows.Count).End(xlUp).Row + 1
 
  Range("A" & lr).Value = Range("B3").Value
  Range("B" & lr).Value = Range("D3").Value
  Range("C" & lr).Value = Range("F3").Value
  Range("D" & lr).Value = Range("H3").Value
  Range("E" & lr).Value = Range("B5").Value
  Range("F" & lr).Value = Range("B7").Value
  Range("G" & lr).Value = Range("B9").Value
  Range("H" & lr).Value = Range("B11").Value
  Range("I" & lr).Value = Range("B12").Value
  Range("J" & lr).Value = Range("B13").Value
  Range("K" & lr).Value = Range("B14").Value
  [B3,D3,F3,H3,B5,B7,B9,B11,B12,B13,B14].Value = ""

End If
       
Application.EnableEvents = True
       
End Sub
 
Hui said:
Try this modification to your code
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$N$3" Then
  V = Application.Match(Target.Value, Range("A21", [A21].End(xlDown)), 0)

  If IsNumeric(V) Then
    V = 20 + V
    [B3].Value = Target.Value
    [D3].Value = Cells(V, 2).Value
    [F3].Value = Cells(V, 3).Value
    [H3].Value = Cells(V, 4).Value
    [B5].Value = Cells(V, 5).Value
    [B7].Value = Cells(V, 6).Value
    [B9].Value = Cells(V, 7).Value
    [B11].Value = Cells(V, 8).Value
    [B12].Value = Cells(V, 9).Value
    [B13].Value = Cells(V, 10).Value
    [B14].Value = Cells(V, 11).Value
  Else
    If Target.Value > "" Then Beep
      [B3,D3,F3,H3,B5,B7,B9,B11,B12.B13.B14].Value = ""
    End If

ElseIf Target.Address = "$B$13" Then

  Dim lr As Integer
  lr = Range("A" & Rows.Count).End(xlUp).Row + 1

  Range("A" & lr).Value = Range("B3").Value
  Range("B" & lr).Value = Range("D3").Value
  Range("C" & lr).Value = Range("F3").Value
  Range("D" & lr).Value = Range("H3").Value
  Range("E" & lr).Value = Range("B5").Value
  Range("F" & lr).Value = Range("B7").Value
  Range("G" & lr).Value = Range("B9").Value
  Range("H" & lr).Value = Range("B11").Value
  Range("I" & lr).Value = Range("B12").Value
  Range("J" & lr).Value = Range("B13").Value
  Range("K" & lr).Value = Range("B14").Value
  [B3,D3,F3,H3,B5,B7,B9,B11,B12,B13,B14].Value = ""

End If
     
Application.EnableEvents = True
     
End Sub
Click to expand...

This code will add a brand new record to the source table which it is not what I want. I want the original record to be updated with the unit number. Sorry if I wasn't clear in my first post.
 
Last edited:
This will update the records

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$N$3" Then
  V = Application.Match(Target.Value, Range("A21", [A21].End(xlDown)), 0)

  If IsNumeric(V) Then
    V = 20 + V
    [B3].Value = Target.Value
    [D3].Value = Cells(V, 2).Value
    [F3].Value = Cells(V, 3).Value
    [H3].Value = Cells(V, 4).Value
    [B5].Value = Cells(V, 5).Value
    [B7].Value = Cells(V, 6).Value
    [B9].Value = Cells(V, 7).Value
    [B11].Value = Cells(V, 8).Value
    [B12].Value = Cells(V, 9).Value
    [B13].Value = Cells(V, 10).Value
    [B14].Value = Cells(V, 11).Value
  Else
    If Target.Value > "" Then Beep
      [B3,D3,F3,H3,B5,B7,B9,B11,B12.B13.B14].Value = ""
    End If

ElseIf Target.Address = "$B$13" Then

  Dim lr As Integer
  lr = Application.Match([B3].Value, Range("A21", [A21].End(xlDown)), 0) + 20

  Range("A" & lr).Value = Range("B3").Value
  Range("B" & lr).Value = Range("D3").Value
  Range("C" & lr).Value = Range("F3").Value
  Range("D" & lr).Value = Range("H3").Value
  Range("E" & lr).Value = Range("B5").Value
  Range("F" & lr).Value = Range("B7").Value
  Range("G" & lr).Value = Range("B9").Value
  Range("H" & lr).Value = Range("B11").Value
  Range("I" & lr).Value = Range("B12").Value
  Range("J" & lr).Value = Range("B13").Value
  Range("K" & lr).Value = Range("B14").Value
  [B3,D3,F3,H3,B5,B7,B9,B11,B12,B13,B14].Value = ""

End If
     
Application.EnableEvents = True
     
End Sub

It is triggered when you change cell B13
 
Thanks Hui. It works. However, I did modify the code a little so the form still shows the updated record after I updated cell value B13. And B14 shows the new "Total Cost" in column K as I have formula written in there. Here is the revised code. I am just wondering if there are 20 fields need to update, then I will have to create 20 if statements to update the source table?

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$N$3" Then
  V = Application.Match(Target.Value, Range("A21", [A21].End(xlDown)), 0)

  If IsNumeric(V) Then
    V = 20 + V
    [B3].Value = Target.Value
    [D3].Value = Cells(V, 2).Value
    [F3].Value = Cells(V, 3).Value
    [H3].Value = Cells(V, 4).Value
    [B5].Value = Cells(V, 5).Value
    [B7].Value = Cells(V, 6).Value
    [B9].Value = Cells(V, 7).Value
    [B11].Value = Cells(V, 8).Value
    [B12].Value = Cells(V, 9).Value
    [B13].Value = Cells(V, 10).Value
    [B14].Value = Cells(V, 11).Value
  Else
    If Target.Value > "" Then Beep
      [B3,D3,F3,H3,B5,B7,B9,B11,B12.B13.B14].Value = ""
    End If

ElseIf Target.Address = "$B$13" Then

  Dim lr As Integer
  lr = Application.Match([B3].Value, Range("A21", [A21].End(xlDown)), 0) + 20

  'Range("A" & lr).Value = Range("B3").Value
  'Range("B" & lr).Value = Range("D3").Value
  'Range("C" & lr).Value = Range("F3").Value
  'Range("D" & lr).Value = Range("H3").Value
  'Range("E" & lr).Value = Range("B5").Value
  'Range("F" & lr).Value = Range("B7").Value
  'Range("G" & lr).Value = Range("B9").Value
  'Range("H" & lr).Value = Range("B11").Value
  'Range("I" & lr).Value = Range("B12").Value
  Range("J" & lr).Value = Range("B13").Value
  [B14].Value = Range("K" & lr).Value
  'Range("K" & lr).Value = Range("B14").Value
  '[B3,D3,F3,H3,B5,B7,B9,B11,B12,B13,B14].Value = ""


End If
   
Application.EnableEvents = True
   
End Sub
 
There are 100+ data fields need to be shown in a form at the top of the sheet. I would say about 50% or more of data fields require user input. Is it possible to tell the program that if a cell has the "orange" color background, then the cell is read only and if a cell has the "yellow" color background, then the cell require user input which the input data will get to send to the respective cell of the source data table located at the bottom of the sheet.

Will select case is a better option over the elseif statement in this case?
 
Why complicate things

It is just as easy to update all the records, rather than worry about trying to update just a few records

also you are calculating values, so you don't want incorrect data stored which wont be correct
 
You must log in or register to reply here.
Back
Top