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 Campaigns Resources


        • Desk Community Learning Series


        • Digest


        • Functions


        • Meetups


        • Kbase


        • Resources


        • Glossary


        • Desk Marketplace


        • MVP Corner


        • Word of the Day


        • Ask the Experts


          • Sticky Posts

          • Introducing a smarter, faster, and more flexible charting experience

            Hello Zoho Sheet users, We're delighted to share the latest news about a major update to charts in Zoho Sheet! The new version supports dynamic data ranges, granular styling options, faster loading, and other interesting enhancements that allow you to
          • What's new in Zoho Sheet: Simplify data entry and collaboration

            Hello, Zoho Sheet community! Last year, our team was focused on research and development so we could deliver updates that enhance your spreadsheet experience. This year, we’re excited to deliver those enhancements—but we'll be rolling them out incrementally
          • New Features: Repeat Last Action, Insert Cut/Copied Rows/Columns and Hyperlink

            You might have noticed the constant updates to Zoho Sheet of late. Here are 3 more features that have been added to Zoho Sheet recently: F4 - Repeat Last Action Insert Cut/Copied Rows and Columns Insert Hyperlink Here is a screen cast demonstrating each of these features. Read further below to learn more about these new features. F4 - Repeat Last Action: You can now repeat the last action you made on your spreadsheet by using the keyboard shortcut, F4. It is quite handy and helps you get your work
          • Introducing Forms in Zoho Sheet

            We hereby bring you the power of ​forms in Zoho Sheet. ​Now, build and create your own customized forms using Zoho Sheet. Be it compiling a questionnaire or rolling out a survey, Zoho Sheet can do it all for you. Forms is an excellent feature that helps you collect information in the simplest of ways and having it in Zoho Sheet takes it a notch higher. Build Simple yet Powerful forms Building forms using Zoho Sheet is fairly simple. The exclusive 'Form' tab lets you create one quickly. Whether you
          • Slicers are now available in Zoho Sheet—filter your data interactively

            At Zoho Sheet, we diligently track user requests and feedback. In line with this, based on extensive user requests, we've integrated Slicers to pivot tables and are delighted to announce its release. Slicers are interactive visual filters that have add,

          Zoho CRM Plus Resources

            Zoho Books Resources


              Zoho Subscriptions Resources

                Zoho Projects Resources


                  Zoho Sprints Resources


                    Zoho Orchestly Resources


                      Zoho Creator Resources


                        Zoho WorkDrive 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




                            Zoho Writer Writer

                            Get Started. Write Away!

                            Writer is a powerful online word processor, designed for collaborative work.

                              Zoho CRM コンテンツ



                                ご検討中の方

                                  • Recent Topics

                                  • Turning off the recorded welcome in Zoho Webinar

                                    Is there a way to turn off the recorded voice that comes up at the beginning of every webinar session? It devalues the experience for attendees from feedback, interrupting their connection with our brand and delaying webinar start unnecessarily.
                                  • Issue with attaching files to a task through the API

                                    Hello! I've implemented a function that creates a task for every new bill that is created but I haven't been able to attach to the task the files which are attached to the bill. I have encountered multiple errors but the most common one is error 33003:
                                  • Tip #68- Exploring technician console: Remote Audio- 'Insider Insights'

                                    Hello Zoho Assist Community! Imagine being in the middle of a remote support session for a media production studio. The editor reports that their audio mixing software is behaving strangely, exported files sound distorted, and a persistent error tone
                                  • Copy all reports in a folder

                                    I currently have a database that I need to create multiple charts filtered by market. All of the charts are identical, I just change to of the filters and then I have the next market's set of charts. The only way I've been able to copy charts (reports)
                                  • Bin Tracking with eBay integration

                                    When trying to setup bin tracking on items that are linked to eBay listings using the built-in Zoho eBay integration, I keep getting this error: "Storage Tracking cannot be enabled for items that are manually fulfiled or for serial/batch items tracked
                                  • Allow Zia Agents using Zoho One Account

                                    When I went to try Zia Agents, it forced the creation of a new, separate account. This seems counter-intuitive to me. I don't want to manage a separate account. Plus, aren't Zoho One users the ones most likely to adopt Zia Agents most quickly? It seems
                                  • Make Camera Overlay & Recording Controls Visible in All Screen-Sharing Options

                                    Hi Zoho WorkDrive Team, Hope you are doing well. We would like to request an improvement to the screen-recording experience in Zoho WorkDrive. Current Limitation: At the moment the recording controls are visible only inside the Zoho WorkDrive tab. When
                                  • ID:2406331

                                    Hello, I have sent the form (ID:2406331) but no answer after two days. This is a paid subscription, I am not allowed to use the eighth paid slot of 50GB email space anymore, only 7 are being used. Is anyone going to help me please so I can use the eighth
                                  • 商談の特定ステージ選択時に、集計項目の入力を必須化したい

                                    こんにちは。Zoho CRMの商談管理で以下を実現したいです。レイアウトルールでは、集計項目を条件に選択できず実現できておりません。よい方法があればご教示いただけると嬉しいです。 ■やりたいこと 商談で特定のステージに変更する場合に、特定の集計項目(同じ商談データ内の項目。以降、集計項目Aとする)を入力必須化したい。 ■概要、状況 ・集計項目Aは、別のオブジェクトで、商談に関連する月ごとの売上を集計している数値項目 ・見積を顧客に提示した際に、集計項目Aに見積金額と一致するようにデータ登録する運用をしている
                                  • Setting default From address when replying to request

                                    At the moment, if I want to reply to a request, the From field has three options, company@zohosupport.com, support@company.zohosupport.com, and support@company.com.  The first two are really internal address that should never be seen by the customer and
                                  • Allow Multiple Scheduled Appointments with Zoho Support

                                    Dear Zoho Team, I hope you're doing well. First, thank you for introducing the option to schedule support calls via the Zoho CRM booking link. This has been a fantastic enhancement, eliminating the need for back-and-forth coordination when scheduling
                                  • Share Edit while in Workflow

                                    There are times that while a writer document is in the middle of a workflow the user that's responsible for that stage of the workflow needs to bring in other people to work on the document. Currently there is no way for the user to be able to allow other's
                                  • How to add a custom hyperlinked button in ZohoCRM Canvas Builder?

                                    I am building custom views in ZohoCRM Canvas builder, with one view that will be used for a client Portal. Trying to add a custom button that when clicked opens a separate window to a ZohoForm. How do I do this?
                                  • Event Time Zone in meeting invites are confusing users

                                    When sending calendar invites to internal and external users, the first section "Event Time Zone" is confusing people and they are automatically declining events. Can this section please be removed??? It already shows the correct time zone next to the
                                  • How do you create an event/meeting in a different time zone?

                                    Does anyone know how do you create an event/meeting in a different time zone? 
                                  • Extend GitHub Commit Integration to Tasks in Zoho Projects

                                    Hello Zoho Projects Team, We hope you are doing well. We would like to submit a feature request regarding the current GitHub integration in Zoho Projects, specifically the ability to link commit information. Current Limitation: As of now, Zoho Projects
                                  • Add Native GitHub Pull Request Creation & Management from Tasks/Bugs

                                    Hi Zoho Projects Team, I am submitting a feature request to enhance our development lifecycle by enabling native GitHub Pull Request (PR) creation and management directly within Zoho Projects. The Current Limitation: Currently, the GitHub integration
                                  • Add Actionable “Smart Commits” for GitHub Integration

                                    Hi Zoho Projects Team, I am submitting a feature request to introduce Smart Commits support for the GitHub integration in Zoho Projects. The Current Limitation: Currently, Zoho Projects offers a "passive" integration. Including a Task ID in a commit message
                                  • Add Unified Project-Wide Development & Release GitHub Dashboard

                                    Hi Zoho Projects Team, I am writing to submit a feature request for a Unified Development & Release GitHub Dashboard that provides centralized GitHub visibility at both the Project and Milestone levels. The Vision: We need a single "Command Center" that
                                  • Add Native “Create GitHub Branch” Action for Tasks and Bugs

                                    Hi Zoho Projects Team, I would like to submit a feature request to enhance the GitHub integration in Zoho Projects by adding the ability to initiate a GitHub branch directly from a Task or Bug. The Current Limitation: Currently, Zoho Projects lacks a
                                  • One-click “Open in VS Code” from Zoho Tasks/Bugs with automatic branch checkout

                                    Hi Zoho Projects Team, I am submitting a feature request to enhance the development workflow within Zoho Projects, specifically the ability to open a Task or Bug directly in VS Code and automatically check out the related branch, similar to capabilities
                                  • How to reduce inventory valuation when not tracking individual items?

                                    We purchase clothing items wholesale, embroider them with a company's logo, then resell. The garment style, size, color, etc. changes according to a customer's desire, so we don't track the individual item's purchase within Zoho Inventory unless there's
                                  • Merge tag didn't work in the campaign sent to test email list

                                    Hello, I just sent out a campaign to my list of test emails (including my personal one). The merge tag that's supposed to put a first name of the contact $[UD:FIRST_NAME||]$ didn't work. Where the first name of the contact should be displayed there's
                                  • Add Camera Background Blur During Recording

                                    Hi Zoho WorkDrive Team, Hope everything is well. We would like to request an enhancement to the video recording feature in Zoho WorkDrive. Currently, the camera preview displayed during a recording does not support background blur. This is an essential
                                  • Sync Data from MA to CRM

                                    Currently, it's a one-way sync of data from the CRM to MA. I believe we should have the ability to select fields to sync from MA to the CRM. The lead score is a perfect example of this. In an ideal world we would be able to impact the lead score of a
                                  • How can I transfer data from Production to Development environment?

                                    Hi, I am using Creator V6 and would like to bring all the data in production to the Development and Testing environments? Is there an easy way of doing that or I have to export and import each table?
                                  • filter on sheets mobile (iOS, iPadOS) does not work

                                    re-posting this as a question because reporting as a problem netted zero responses... I have this issue on every spreadsheet, whether imported or created natively in Zoho Sheets. I can use on desktop without issue: but on iOS the filter dropdowns are
                                  • How to map fields from Zoho Recruit to Zoho People

                                    I've got these fields from my Job Offer that I'm trying to map to the Work information fields in Zoho People, but they arent showing up. For example, how do I get the department name field (in the job post) to map to the work information field in Zoho
                                  • Mass email from Report output

                                    Hi, I'd like to send a mass email based on a report output. The report is pulling multiple information from linked modules. Each line of the report ends up with a contact name, email and multiple field values pulled from the linked modules (some are custom
                                  • Introducing SlyteUI : From Idea to a Working Interface in Minutes

                                    Hello everyone! Are you spending hours building basic UIs? Does even the smallest customization feel like a major task? CRM customization should feel intuitive and straightforward, not time consuming or exhausting. SlyteUI makes this possible by simplifying
                                  • Add Comprehensive Accessibility Features to Zoho Writer

                                    Hello Zoho Writer Team, We hope you are doing well. We would like to submit a feature request to enhance Zoho Writer with a full set of accessibility tools, similar to the accessibility options already available in the Zoho Desk agent interface. 🚧 Current
                                  • How to see Tickets that require a response

                                    When an agent checks his work he should be able to see a list of tickets that he has to reply to. Currently he can only see if the ticket is closed or open. However the ticket might be open but we are waiting for the customers reply, so there is nothing
                                  • Zoho Books' 2025 Wrapped

                                    Before we turn the page to a new year, it’s time to revisit the updates that made financial management simpler and more intuitive. This annual roundup brings together the most impactful features and enhancements we delivered in 2025, offering a clear
                                  • [Free webinar] Integrations in Zoho Creator - Creator Tech Connect

                                    Hello everyone, We’re excited to invite you to another edition of the Creator Tech Connect webinar. About Creator Tech Connect The Creator Tech Connect series is a free monthly webinar featuring in-depth technical sessions designed for developers, administrators,
                                  • Trigger workflows from SLA escalations in Zoho Desk?

                                    Hey everyone, I’m currently working with SLA escalation rules in Zoho Desk and ran into a limitation that I’m hoping someone here has solved more elegantly. As far as I can tell, SLA escalations only support fairly limited actions (like changing the ticket
                                  • Can I hide empty Contact fields from view?

                                    Some contacts have a lot of empty fields, others are mostly filled. Is there a way I can hide/show empty fields without changing the actual Layout? I would like to de-clutter my view, and also be able to add information later as I am able. I would be
                                  • Agents permission per department

                                    Hi Team, can I setup permission for each agent what they can do in each department, for example I want account department agents to only have view access to support department tickets and not allowed to assign or reply to clients. I am sure this would
                                  • Ask the Experts - Live Q&A webinar

                                    Hello Community, We’re excited to host our very first Ask the Experts session! Join us on 7 April 2026 from 11 a.m. to 12 p.m. (IST) for this live webinar Q&A session, where you will have an opportunity to connect directly with our product experts, gain
                                  • Cliq iOS can't see shared screen

                                    Hello, I had this morning a video call with a colleague. She is using Cliq Desktop MacOS and wanted to share her screen with me. I'm on iPad. I noticed, while she shared her screen, I could only see her video, but not the shared screen... Does Cliq iOS is able to display shared screen, or is it somewhere else to be found ? Regards
                                  • Prevent Automatic Milestone Inheritance for Newly Created Task Lists

                                    Hello Zoho Projects Team, We hope you are doing well. We would like to request an enhancement regarding how new task lists inherit Milestone association in Zoho Projects. Current Behavior: At the moment, when a new task list is created below an existing
                                  • Next Page