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)
On Tue, 23 Sep 2008 13:32:52 -0700, "Bob" <(E-Mail Removed)> wrote:
>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
>
Similar Threads
