Sheet Custom Property – Set Sheet Custom Property

Sets a custom property on the specified Excel.Worksheet . 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 provided value. Any exceptions are caught silently.

    Public Function xlSetSheetCustomProperty(wsSheet As Worksheet, 
                                             PropertyName As String, 
                                             Value As String) As Boolean

        Dim customProperties As CustomProperties = wsSheet.CustomProperties
        Dim propToDelete As CustomProperty = Nothing

        'Delete if exists, change value not allowed - must enumerate to find the property, direct access by name not working
        Try
            For Each cp As CustomProperty In customProperties
                If cp.Name = PropertyName Then
                    propToDelete = cp
                    Exit For
                End If
            Next

            If propToDelete IsNot Nothing Then
                propToDelete.Delete()
            End If
        Catch ex As Exception : End Try

        Try
            customProperties.Add(PropertyName, Value)
            Return True
        Catch ex As Exception
            'ShowMessageBox($"Error: {ex.Message}")
            Return False
        End Try

    End Function

    Public Function xlSetSheetCustomProperty(wsSheet As Worksheet, 
                                             PropertyName As String, 
                                             Value As String) As Boolean

        Dim customProperties As CustomProperties = wsSheet.CustomProperties
        Dim propToDelete As CustomProperty = Nothing

        'Delete if exists, change value not allowed - must enumerate to find the property, direct access by name not working
        Try
            For Each cp As CustomProperty In customProperties
                If cp.Name = PropertyName Then
                    propToDelete = cp
                    Exit For
                End If
            Next

            If propToDelete IsNot Nothing Then
                propToDelete.Delete()
            End If
        Catch ex As Exception : End Try

        Try
            customProperties.Add(PropertyName, Value)
            Return True
        Catch ex As Exception
            'ShowMessageBox($"Error: {ex.Message}")
            Return False
        End Try

    End Function

Posted by ipercube