Trigger a custom function in a Zoho Sheet on range change

Trigger a custom function in a Zoho Sheet on range change

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!




                            Zoho Desk Resources

                            • Desk Community Learning Series


                            • Digest


                            • Functions


                            • Meetups


                            • Kbase


                            • Resources


                            • Glossary


                            • Desk Marketplace


                            • MVP Corner


                            • Word of the Day



                                Zoho Marketing Automation
                                        • Sticky Posts

                                        • Check out the new Checkbox in Zoho Sheet

                                          Checkbox, the simple GUI widget that lets you choose between two mutually exclusive options i.e Yes or No, is now in Zoho Sheet. Having made it across the web, Checkbox is familiar but is still elusive to most spreadsheet software. Special developer ribbons, macros and all other complexities to add a checkbox to a spreadsheet? Not in Zoho Sheet. How to add Checkbox to Zoho Sheet? Adding a Checkbox in Zoho Sheet is simple. Select a cell/range and choose "Checkbox" in "Input Controls" under the Format
                                        • Format and analyze spreadsheet data with Zoho Sheet's Table

                                          While a spreadsheet helps you compile data for easy analysis, formatting helps make your data more comprehensible. We are happy to announce the release of Table, a crucial tool for improving data formatting in Zoho Sheet. More than a formatting tool,
                                        • Introducing Data Bars: Graphically represent changes in data within the cells

                                          Conditional formatting has helped millions of spreadsheet users analyze and highlight their data more efficiently. In addition to the classic rules, color scales, and icon sets available in Zoho Sheet, you can now apply Data Bars, a convenient method
                                        • Zia in Zoho Sheet now makes suggestions on conditional formats, picklists, and checkboxes

                                          Zia, Zoho's popular AI assistant, has been helping users succeed across Zoho applications since inception. In Zoho Sheet, Zia helps users efficiently build reports and analyze data with recommendations about data cleaning, charts, and pivot table. Also,
                                        • Revamped Print in Zoho Sheet—customized for paper

                                          The latest enhancements to Zoho Sheet's Print settings allows you to print a worksheet or a selected range, with customizations that make the data comprehensible, even on paper.   Customize header and footer Make your spreadsheets meaningful with a relevant header and footer. You can now choose to display the file name, sheet name, current page number, total pages, date, and time as the header and footer in your print view. Besides the predefined elements, you can also give a custom text for header/footer.


                                        Manage your brands on social media



                                              Zoho TeamInbox Resources

                                                Zoho DataPrep Resources



                                                  Zoho CRM Plus Resources

                                                    Zoho Books Resources


                                                      Zoho Subscriptions Resources

                                                        Zoho Projects Resources


                                                          Zoho Sprints Resources


                                                            Qntrl Resources


                                                              Zoho Creator Resources


                                                                Zoho WorkDrive Resources



                                                                  Zoho Campaigns Resources

                                                                    Zoho CRM Resources

                                                                    • CRM Community Learning Series

                                                                      CRM Community Learning Series


                                                                    • Tips

                                                                      Tips

                                                                    • Functions

                                                                      Functions

                                                                    • Meetups

                                                                      Meetups

                                                                    • Kbase

                                                                      Kbase

                                                                    • Resources

                                                                      Resources

                                                                    • Digest

                                                                      Digest

                                                                    • CRM Marketplace

                                                                      CRM Marketplace

                                                                    • MVP Corner

                                                                      MVP Corner

                                                                    





                                                                    




                                                                        Design. Discuss. Deliver.

                                                                        Create visually engaging stories with Zoho Show.

                                                                        Get Started Now