Hello,
I have a custom function in Zoho Sheet that updates a Zoho CRM record. That by itself works. I am trying to trigger that function when a new row is added to the sheet. I thought this would be able to happen via the VBA editor, but I can't get it to work. It did work when the cells were hard coded, but then when using offset I couldn't get it to work.
I have pretty much no experience with VBA. But here is what worked:
REM Sheet Module
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = false
On Error Goto ENEV
REM add your code below
Set watchRange = Range("L2:L1000")
If Not Intersect(Target,watchRange) Is Nothing Then
If Target.Value <> Nothing Then
Range(M2).Select
ActiveCell.Formula="=_CREATE_UPDATE_OPP(A2,C2,D2)"
End If
End If
ENEV:
Application.EnableEvents = true
End Sub
When I attempted to enter offsets, so that it would get the values from the newly added row, I had no luck. No idea what I'm doing, just tried to use offset as I read about it in another article.
Didn't work:
REM Sheet Module
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = false
On Error Goto ENEV
REM add your code below
Set watchRange = Range("L2:L1000")
If Not Intersect(Target,watchRange) Is Nothing Then
If Target.Value <> Nothing Then
Range((Target.Value).Offset(0,1)).Select
ActiveCell.Formula="=_CREATE_UPDATE_OPP(Target.Value.Offset(0,-11),Target.Value.Offset(0,-9),Target.Value.Offset(0,-8)"
End If
End If
ENEV:
Application.EnableEvents = true
End Sub
I also tried () around each offset reference. There is probably a way better way to do this, so if so, disregard what I've started. Thank you for your help!