{"id":816,"date":"2020-05-11T19:12:21","date_gmt":"2020-05-11T19:12:21","guid":{"rendered":"http:\/\/www.vbastring.com\/blog\/?p=816"},"modified":"2020-05-11T19:16:17","modified_gmt":"2020-05-11T19:16:17","slug":"how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted","status":"publish","type":"post","link":"https:\/\/vbastring.com\/blog\/2020\/05\/11\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\/","title":{"rendered":"How To Trigger An Excel VBA Event When Cell Value Is Deleted"},"content":{"rendered":"<p>This post actually was birthed from a comment made on one on my Youtube videos:<\/p>\n<blockquote><p>I wanted to know as how to trigger an event when a cell value is deleted?<\/p><\/blockquote>\n<p><em>(a comment to this post&#8217;s video: http:\/\/www.vbastring.com\/blog\/how-to-run-a-macro-when-cell-value-changes-in-excel-vba\/)<\/em><\/p>\n<p>So we are going to trigger an Excel VBA event when a value is deleted from a cell.<\/p>\n<p><a href=\"https:\/\/i0.wp.com\/www.vbastring.com\/blog\/wp-content\/uploads\/2020\/05\/excel-vba-event.png\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.vbastring.com\/blog\/wp-content\/uploads\/2020\/05\/excel-vba-event.png?resize=186%2C455\" alt=\"\" width=\"186\" height=\"455\" class=\"aligncenter size-full wp-image-820\" \/><\/a><\/p>\n<p>In the above screen shot we have a fake list of names in column A of the worksheet.<\/p>\n<p><a href=\"https:\/\/i0.wp.com\/www.vbastring.com\/blog\/wp-content\/uploads\/2020\/05\/excel-vba-event2.png\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.vbastring.com\/blog\/wp-content\/uploads\/2020\/05\/excel-vba-event2.png?resize=746%2C466\" alt=\"\" width=\"746\" height=\"466\" class=\"aligncenter size-full wp-image-821\" \/><\/a><\/p>\n<p>We are deleting the value from Cell &#8220;A4&#8221;, and we are adding a little confirmation message to confirm to the user that that&#8217;s what they want to do.<\/p>\n<p>If we click &#8220;Yes&#8221;, the old value (existing value) gets &#8220;deleted&#8221; (substituted) with the current value (a blank value).<\/p>\n<pre class=\"lang:vb decode:true \" >\r\nPrivate Sub Worksheet_Change(ByVal Target As Range)\r\n    'this was modified from an idea from https:\/\/stackoverflow.com\/questions\/4668410\/how-do-i-get-the-old-value-of-a-changed-cell-in-excel-vba\r\n    \r\n    \r\n    Static blnAlreadyBeenHere As Boolean\r\n    Dim intAnswer As Integer\r\n    \r\n    'This piece avoid to execute Worksheet_Change again\r\n    If blnAlreadyBeenHere Then\r\n        blnAlreadyBeenHere = False\r\n        Exit Sub\r\n    End If\r\n    \r\n    'Now, we will create variant variables store the old and new value\r\n    Dim varOldValue As Variant\r\n    Dim varNewValue As Variant\r\n    \r\n    'Use this to store new value\r\n    varNewValue = Target.Value\r\n    \r\n    'Use the 'undo' functionality to retrieve the old value\r\n    \r\n    'Here we will tell the Worksheet_Change event to avoid calling a new Worksheet_Change execution\r\n    blnAlreadyBeenHere = True\r\n    \r\n    Application.Undo\r\n    \r\n    'now we can store the old value\r\n    varOldValue = Target.Value\r\n    \r\n    'Now rewrite the cell with the new value stored earlier\r\n    \r\n    'Here again we will tell the Worksheet_Change event to avoid calling a new Worksheet_Change execution\r\n    blnAlreadyBeenHere = True\r\n    Target.Value = varNewValue\r\n    \r\n    '***************************************************************\r\n    'Now we have the 2 values stored in varOldValue and varNewValue\r\n    '***************************************************************\r\n\r\n    'Check if the cell value was deleted:\r\n    \r\n    If varNewValue = \"\" Then\r\n        intAnswer = MsgBox(\"Delete the current cell's value?\", vbYesNo, \"Confirmation\")\r\n        If intAnswer = vbNo Then\r\n            'Here again we will tell the Worksheet_Change event to avoid calling a new Worksheet_Change execution\r\n            blnAlreadyBeenHere = True\r\n            \r\n            'set the deleted cell value to what it was before the deletion. (couldn't use undo)\r\n            Target.FormulaR1C1 = varOldValue\r\n            \r\n            'Or, fire the event you want, like logging the deletion in another file.\r\n            \r\n        End If\r\n    Else\r\n        'Just for reference:\r\n        Debug.Print \"oldval: \" &amp; varOldValue &amp; \", newval: \" &amp; varNewValue\r\n    End If\r\n\r\n\r\nEnd Sub\r\n<\/pre>\n<p>Do you have questions?  <\/p>\n<p>Let me know.  Also, share this with someone else.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This post actually was birthed from a comment made on one on my Youtube videos: I wanted to know as how to trigger an event when a cell value is deleted? (a comment to this post&#8217;s video: http:\/\/www.vbastring.com\/blog\/how-to-run-a-macro-when-cell-value-changes-in-excel-vba\/) So we are going to trigger an Excel VBA event when a value is deleted from a [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"nf_dc_page":"","om_disable_all_campaigns":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[],"tags":[],"class_list":["post-816","post","type-post","status-publish","format-standard","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>How To Trigger An Excel VBA Event When Cell Value Is Deleted - My Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vbastring.com\/blog\/2020\/05\/11\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How To Trigger An Excel VBA Event When Cell Value Is Deleted - My Blog\" \/>\n<meta property=\"og:description\" content=\"This post actually was birthed from a comment made on one on my Youtube videos: I wanted to know as how to trigger an event when a cell value is deleted? (a comment to this post&#8217;s video: http:\/\/www.vbastring.com\/blog\/how-to-run-a-macro-when-cell-value-changes-in-excel-vba\/) So we are going to trigger an Excel VBA event when a value is deleted from a [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vbastring.com\/blog\/2020\/05\/11\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\/\" \/>\n<meta property=\"og:site_name\" content=\"My Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-05-11T19:12:21+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-05-11T19:16:17+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2020\/05\/excel-vba-event.png\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2020\\\/05\\\/11\\\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2020\\\/05\\\/11\\\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\\\/\"},\"author\":{\"name\":\"admin\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/#\\\/schema\\\/person\\\/e1de0b30e98940381697872449c341f5\"},\"headline\":\"How To Trigger An Excel VBA Event When Cell Value Is Deleted\",\"datePublished\":\"2020-05-11T19:12:21+00:00\",\"dateModified\":\"2020-05-11T19:16:17+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2020\\\/05\\\/11\\\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\\\/\"},\"wordCount\":153,\"image\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2020\\\/05\\\/11\\\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/www.vbastring.com\\\/blog\\\/wp-content\\\/uploads\\\/2020\\\/05\\\/excel-vba-event.png\",\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2020\\\/05\\\/11\\\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\\\/\",\"url\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2020\\\/05\\\/11\\\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\\\/\",\"name\":\"How To Trigger An Excel VBA Event When Cell Value Is Deleted - My Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2020\\\/05\\\/11\\\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2020\\\/05\\\/11\\\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/www.vbastring.com\\\/blog\\\/wp-content\\\/uploads\\\/2020\\\/05\\\/excel-vba-event.png\",\"datePublished\":\"2020-05-11T19:12:21+00:00\",\"dateModified\":\"2020-05-11T19:16:17+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/#\\\/schema\\\/person\\\/e1de0b30e98940381697872449c341f5\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2020\\\/05\\\/11\\\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/vbastring.com\\\/blog\\\/2020\\\/05\\\/11\\\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2020\\\/05\\\/11\\\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\\\/#primaryimage\",\"url\":\"http:\\\/\\\/www.vbastring.com\\\/blog\\\/wp-content\\\/uploads\\\/2020\\\/05\\\/excel-vba-event.png\",\"contentUrl\":\"http:\\\/\\\/www.vbastring.com\\\/blog\\\/wp-content\\\/uploads\\\/2020\\\/05\\\/excel-vba-event.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2020\\\/05\\\/11\\\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How To Trigger An Excel VBA Event When Cell Value Is Deleted\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/\",\"name\":\"My Blog\",\"description\":\"My WordPress Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/#\\\/schema\\\/person\\\/e1de0b30e98940381697872449c341f5\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g\",\"caption\":\"admin\"},\"sameAs\":[\"https:\\\/\\\/vbastring.com\\\/blog\"],\"url\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/author\\\/admin\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How To Trigger An Excel VBA Event When Cell Value Is Deleted - My Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vbastring.com\/blog\/2020\/05\/11\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\/","og_locale":"en_US","og_type":"article","og_title":"How To Trigger An Excel VBA Event When Cell Value Is Deleted - My Blog","og_description":"This post actually was birthed from a comment made on one on my Youtube videos: I wanted to know as how to trigger an event when a cell value is deleted? (a comment to this post&#8217;s video: http:\/\/www.vbastring.com\/blog\/how-to-run-a-macro-when-cell-value-changes-in-excel-vba\/) So we are going to trigger an Excel VBA event when a value is deleted from a [&hellip;]","og_url":"https:\/\/vbastring.com\/blog\/2020\/05\/11\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\/","og_site_name":"My Blog","article_published_time":"2020-05-11T19:12:21+00:00","article_modified_time":"2020-05-11T19:16:17+00:00","og_image":[{"url":"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2020\/05\/excel-vba-event.png","type":"","width":"","height":""}],"author":"admin","twitter_card":"summary_large_image","twitter_misc":{"Written by":"admin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vbastring.com\/blog\/2020\/05\/11\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\/#article","isPartOf":{"@id":"https:\/\/vbastring.com\/blog\/2020\/05\/11\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\/"},"author":{"name":"admin","@id":"https:\/\/vbastring.com\/blog\/#\/schema\/person\/e1de0b30e98940381697872449c341f5"},"headline":"How To Trigger An Excel VBA Event When Cell Value Is Deleted","datePublished":"2020-05-11T19:12:21+00:00","dateModified":"2020-05-11T19:16:17+00:00","mainEntityOfPage":{"@id":"https:\/\/vbastring.com\/blog\/2020\/05\/11\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\/"},"wordCount":153,"image":{"@id":"https:\/\/vbastring.com\/blog\/2020\/05\/11\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\/#primaryimage"},"thumbnailUrl":"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2020\/05\/excel-vba-event.png","inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/vbastring.com\/blog\/2020\/05\/11\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\/","url":"https:\/\/vbastring.com\/blog\/2020\/05\/11\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\/","name":"How To Trigger An Excel VBA Event When Cell Value Is Deleted - My Blog","isPartOf":{"@id":"https:\/\/vbastring.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/vbastring.com\/blog\/2020\/05\/11\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\/#primaryimage"},"image":{"@id":"https:\/\/vbastring.com\/blog\/2020\/05\/11\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\/#primaryimage"},"thumbnailUrl":"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2020\/05\/excel-vba-event.png","datePublished":"2020-05-11T19:12:21+00:00","dateModified":"2020-05-11T19:16:17+00:00","author":{"@id":"https:\/\/vbastring.com\/blog\/#\/schema\/person\/e1de0b30e98940381697872449c341f5"},"breadcrumb":{"@id":"https:\/\/vbastring.com\/blog\/2020\/05\/11\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vbastring.com\/blog\/2020\/05\/11\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vbastring.com\/blog\/2020\/05\/11\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\/#primaryimage","url":"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2020\/05\/excel-vba-event.png","contentUrl":"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2020\/05\/excel-vba-event.png"},{"@type":"BreadcrumbList","@id":"https:\/\/vbastring.com\/blog\/2020\/05\/11\/how-to-trigger-an-excel-vba-event-when-cell-value-is-deleted\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vbastring.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How To Trigger An Excel VBA Event When Cell Value Is Deleted"}]},{"@type":"WebSite","@id":"https:\/\/vbastring.com\/blog\/#website","url":"https:\/\/vbastring.com\/blog\/","name":"My Blog","description":"My WordPress Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vbastring.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/vbastring.com\/blog\/#\/schema\/person\/e1de0b30e98940381697872449c341f5","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g","caption":"admin"},"sameAs":["https:\/\/vbastring.com\/blog"],"url":"https:\/\/vbastring.com\/blog\/author\/admin\/"}]}},"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/posts\/816","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/comments?post=816"}],"version-history":[{"count":5,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/posts\/816\/revisions"}],"predecessor-version":[{"id":827,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/posts\/816\/revisions\/827"}],"wp:attachment":[{"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/media?parent=816"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/categories?post=816"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/tags?post=816"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}