I have the following code and function that is supposed to run when the user types/pastes into a cell.
'Insert Depot Memo Data for user
Dim oCell As Range, targetCell As Range
Dim ws2 As Worksheet
On Error GoTo Message
If Not Intersect(Target, Range("C:C")) Is Nothing Then ' <-- run this code only if a value in column I has changed
If Not GetWb("Depot Memo", ws2) Then Exit Sub
With ws2
For Each targetCell In Target
Set oCell = .Range("J1", .Cells(.Rows.Count, "J").End(xlUp)).Find(what:=targetCell.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not oCell Is Nothing Then
Application.EnableEvents = False
targetCell.Offset(0, 1).Value = oCell.Offset(0, 1)
targetCell.Offset(0, 2).Value = oCell.Offset(0, -2)
Application.EnableEvents = True
End If
Next
End With
End If
Function:
Function GetWb(wbNameLike As String, ws As Worksheet) As Boolean
Dim Wb As Workbook
For Each Wb In Workbooks
If Wb.Name Like "*" & wbNameLike & "*" Then '<-- check if workbook name contains "Depot Memo"
Set ws = Wb.Worksheets(1)
Exit For
End If
Next
GetWb = Not ws Is Nothing
End Function
This code works, but it is not launching correctly. As soon as the user types/pastes a value into the cell (once the cell has changed) the code is supposed to run.
At the moment, the code is not working unless the user escapes the cell and then goes back to click on it.
I have this code under a private worksheet selection change event. I don't know if this is right? When i try to put it under a private worksheet change event it doesn't do anything.
Please can someone show me where i am going wrong?
This can be done by checking Worksheet_Change. Provided below is an example which will check cells of a range. In this example A1:C10.
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("A1:C10")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
' Display a message when one of the designated cells has been
' changed.
' Place your code here.
MsgBox "Cell " & Target.Address & " has changed."
End If
End Sub