{"id":415,"date":"2025-10-25T11:14:27","date_gmt":"2025-10-25T09:14:27","guid":{"rendered":"https:\/\/www.silverice.it\/IpercubeTechWP1\/?page_id=415"},"modified":"2025-10-25T11:16:35","modified_gmt":"2025-10-25T09:16:35","slug":"xlsetsheetcustomproperty","status":"publish","type":"page","link":"https:\/\/www.silverice.it\/IpercubeTechWP1\/ipercube-utilities\/excel-functions\/xlsetsheetcustomproperty\/","title":{"rendered":"Sheet Custom Property &#8211; Set Sheet Custom Property"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Sets a custom property on the specified\u00a0<strong>Excel.Worksheet<\/strong>\u00a0. If the property already exists, it is deleted and recreated with the new value.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Excel custom properties on worksheets cannot be updated directly by name, so the function first searches for and deletes any existing property with the same name. Then it adds the new property with the provided value. Any exceptions are caught silently.<\/p>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-8f761849 wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\" style=\"flex-basis:75%\">\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-8f761849 wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column page-snippet is-layout-flow wp-block-column-is-layout-flow\" style=\"flex-basis:100%\">\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-Geist-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-Geist-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">VB<\/span><span role=\"button\" tabindex=\"0\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>    Public Function xlSetSheetCustomProperty(wsSheet As Worksheet, \n                                             PropertyName As String, \n                                             Value As String) As Boolean\n\n        Dim customProperties As CustomProperties = wsSheet.CustomProperties\n        Dim propToDelete As CustomProperty = Nothing\n\n        'Delete if exists, change value not allowed - must enumerate to find the property, direct access by name not working\n        Try\n            For Each cp As CustomProperty In customProperties\n                If cp.Name = PropertyName Then\n                    propToDelete = cp\n                    Exit For\n                End If\n            Next\n\n            If propToDelete IsNot Nothing Then\n                propToDelete.Delete()\n            End If\n        Catch ex As Exception : End Try\n\n        Try\n            customProperties.Add(PropertyName, Value)\n            Return True\n        Catch ex As Exception\n            'ShowMessageBox($\"Error: {ex.Message}\")\n            Return False\n        End Try\n\n    End Function<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">Public Function <\/span><span style=\"color: #DCDCAA\">xlSetSheetCustomProperty<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">wsSheet<\/span><span style=\"color: #D4D4D4\"> As <\/span><span style=\"color: #4EC9B0\">Worksheet<\/span><span style=\"color: #D4D4D4\">, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                             PropertyName As <\/span><span style=\"color: #4EC9B0\">String<\/span><span style=\"color: #D4D4D4\">, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                             Value As <\/span><span style=\"color: #4EC9B0\">String<\/span><span style=\"color: #D4D4D4\">) As <\/span><span style=\"color: #4EC9B0\">Boolean<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #569CD6\">Dim<\/span><span style=\"color: #9CDCFE\"> customProperties <\/span><span style=\"color: #D4D4D4\">As <\/span><span style=\"color: #4EC9B0\">CustomProperties<\/span><span style=\"color: #D4D4D4\"> = wsSheet.CustomProperties<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #569CD6\">Dim<\/span><span style=\"color: #9CDCFE\"> propToDelete <\/span><span style=\"color: #D4D4D4\">As <\/span><span style=\"color: #4EC9B0\">CustomProperty<\/span><span style=\"color: #D4D4D4\"> =<\/span><span style=\"color: #569CD6\"> Nothing<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #6A9955\">&#39;Delete if exists, change value not allowed - must enumerate to find the property, direct access by name not working<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        Try<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #C586C0\">For<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #C586C0\">Each<\/span><span style=\"color: #D4D4D4\"> cp As <\/span><span style=\"color: #4EC9B0\">CustomProperty<\/span><span style=\"color: #D4D4D4\"> In customProperties<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                <\/span><span style=\"color: #C586C0\">If<\/span><span style=\"color: #D4D4D4\"> cp.<\/span><span style=\"color: #9CDCFE\">Name<\/span><span style=\"color: #D4D4D4\"> =<\/span><span style=\"color: #9CDCFE\"> PropertyName<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #C586C0\">Then<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                    <\/span><span style=\"color: #9CDCFE\">propToDelete<\/span><span style=\"color: #D4D4D4\"> =<\/span><span style=\"color: #9CDCFE\"> cp<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                    <\/span><span style=\"color: #C586C0\">Exit For<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                <\/span><span style=\"color: #C586C0\">End If<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #C586C0\">Next<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #C586C0\">If<\/span><span style=\"color: #D4D4D4\"> propToDelete IsNot<\/span><span style=\"color: #569CD6\"> Nothing<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #C586C0\">Then<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                propToDelete.<\/span><span style=\"color: #DCDCAA\">Delete<\/span><span style=\"color: #D4D4D4\">()<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #C586C0\">End If<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        Catch ex As <\/span><span style=\"color: #4EC9B0\">Exception<\/span><span style=\"color: #D4D4D4\"> : <\/span><span style=\"color: #DCDCAA\">End<\/span><span style=\"color: #D4D4D4\"> Try<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        Try<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            customProperties.<\/span><span style=\"color: #DCDCAA\">Add<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">PropertyName<\/span><span style=\"color: #D4D4D4\">,<\/span><span style=\"color: #9CDCFE\"> Value<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #C586C0\">Return<\/span><span style=\"color: #569CD6\"> True<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        Catch ex As <\/span><span style=\"color: #4EC9B0\">Exception<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #6A9955\">&#39;ShowMessageBox($&quot;Error: {ex.Message}&quot;)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #C586C0\">Return<\/span><span style=\"color: #569CD6\"> False<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #DCDCAA\">End<\/span><span style=\"color: #D4D4D4\"> Try<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">End Function<\/span><\/span><\/code><\/pre><\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Sets a custom property on the specified\u00a0Excel.Worksheet\u00a0. If the property already exists, it is deleted and recreated with the new value. Excel custom properties on worksheets cannot be updated directly by name, so the function first searches for and deletes any existing property with the same name. Then it adds the new property with the [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":349,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-415","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages\/415","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/comments?post=415"}],"version-history":[{"count":3,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages\/415\/revisions"}],"predecessor-version":[{"id":419,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages\/415\/revisions\/419"}],"up":[{"embeddable":true,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages\/349"}],"wp:attachment":[{"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/media?parent=415"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}