CustomProperties

[Bob]

Hi Everyone:

I have created a custom property very similar to the example in the help
section of excel. Now, I want to change its value, and I get an error. Can
someone show me how I can make the change.

To create the custom property (works fine):
ActiveSheet.CustomProperties.Add "cmb1", 125

To change the value ( I get the error):
ActiveSheet.CustomProperties.items(1).Value = 2000

So, for the CustomProperty named "cmb1", I want to change the value from 125
to 2000. Thanks for all your help.

Bob

[Chip Pearson]

Bob,

I've found CustomProperties to be a bit flakey, especially when trying
to access a property by name. I use a function called IndexOfProperty
that returns the numerical index into CustomProperties of the
specifiied property name. For example,

Dim N As Long
Dim S As String
N=IndexOfProperty(Worksheets("Sheet1"),"PropName")
With Worksheets("Sheet1").CustomProperties
If N<>0 Then
S=.Item(N).Value
MsgBox "Property 'PropName' has a value of " & Cstr(S)
Else
Msgbox "Property 'PropName' not foundl."
End If
End With

The complete IndexOfProperty is show below:

Function IndexOfProperty(WS As Worksheet, PropertyName As String) As
Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' IndexOfProperty
' Returns the 1-based index number corresponding to a CustomProperty
' of worksheet WS with a property name equal to PropertyName. Returns
' 0 if property name was not found.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim N As Long
With WS.CustomProperties
For N = 1 To .Count
If StrComp(.Item(N).Name, PropertyName, vbTextCompare) = 0
Then
IndexOfProperty = N
Exit Function
End If
Next N
End With
IndexOfProperty = 0 ' not found

End Function

The following coide illustrates deleting, creating, modifying, and
retrieving a CustomProperty:

Sub AAA()

Dim WS As Worksheet
Dim Props As Object
Dim PropName As String
Dim PropValue As Variant
Dim N As Long

Set WS = ActiveSheet
' delete property. ignore error if it doesn't exist.
On Error Resume Next
N = IndexOfProperty(WS, "TheProp")
If N <> 0 Then
WS.CustomProperties(N).Delete
End If
On Error GoTo 0

' add a new property with a value
WS.CustomProperties.Add Name:="TheProp", Value:=12345

' change the property value
N = IndexOfProperty(WS, "TheProp")
If N <> 0 Then
WS.CustomProperties(N).Value = 1357
End If

' retrieve the property
PropName = "TheProp"
PropValue = WS.CustomProperties(N).Value
Debug.Print PropValue
End Sub

Cordially,
Chip Pearson
Mirosoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
The San Diego Project Group, LLC
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)

[Bob]

Thank you Chip. Your code and example showed me a lot of things. Thanks
for your help.

Bob

"Chip Pearson" <ch...@cpearson.com> wrote in message
news:j4nid45l8othv4ucq...@4ax.com...