View Full Version : Update cell on sheet change - w/correct file
RKFerguson
04-23-2016, 05:41 PM
Attached is the "xlsm" file that I should have attached to my first post.
It contains several methods that I am testing, but the one I am having trouble with is the VBA to update a cell when the cell at a target address changes.
Thanks much.
Ferg15989
Paul_Hossler
04-23-2016, 06:02 PM
Maybe something like this
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("K3:K10")
If Application.Intersect(KeyCells, Target) Is Nothing Then Exit Sub
' MsgBox "Cell " & Target.Address & " has changed."
Target.Offset(0, 1).Value = Target.Offset(0, -4).Value
End Sub
RKFerguson
04-23-2016, 06:10 PM
Maybe something like this
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("K3:K10")
If Application.Intersect(KeyCells, Target) Is Nothing Then Exit Sub
' MsgBox "Cell " & Target.Address & " has changed."
Target.Offset(0, 1).Value = Target.Offset(0, -4).Value
End Sub
RKFerguson
04-23-2016, 06:12 PM
Paul,
Thanks for the quick response. I don't want/need to show an alert with the cell(s) change. I was just using the MsgBox so I could see the value of Target.Address.
Thank you also for the code idea/approach. I can't pretend that I fully understand it, but I will try it and study it.
Most appreciative.
Ferg
Paul_Hossler
04-23-2016, 06:19 PM
Yea, I commented out the MsgBox
To step through in words ...
If the changed cell (Target) is not one of the key cells, then exit (you had that)
1 column to the right and in the same row of the Target cell, put the value from the same row, and 4 columns to the left
Actually, I was a little careless ... You should disable events before doing something that might re-trigger the event handler
Didn't matter in this case, but it's good practice
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("K3:K10")
If Application.Intersect(KeyCells, Target) Is Nothing Then Exit Sub
' MsgBox "Cell " & Target.Address & " has changed."
Application.EnableEvents = False
Target.Offset(0, 1).Value = Target.Offset(0, -4).Value
Application.EnableEvents = True
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.