Hi everybody,

is there something wrong with this code
or is something wrong with Excel?

'ActiveSheet.CustomProperties.Add Name:="x", Value:=1 ' ok

MsgBox ActiveSheet.CustomProperties("x").Value ' Type mismatch
MsgBox ActiveSheet.CustomProperties(1).Value ' ok

In Word I can refer to a customdocumentproperty by name.
MsgBox ActiveDocument.CustomDocumentProperties("x").Value ' ok

--
Greetings from Bavaria, Germany
Helmut Weber, MVP WordVBA
"red.sys" & chr(64) & "t-online.de"
Word 2002, Windows 2000 (german versions)

You can do the same in Excel with CustomDOCUMENTProperties

ActiveWorkbook.CustomDocumentProperties.Add Name:="x", _
LinkToContent:=False, Type:=msoPropertyTypeNumber, Value:=1

MsgBox ActiveWorkbook.CustomDocumentProperties("x").Value ' Type mismatch
MsgBox ActiveWorkbook.CustomDocumentProperties(1).Value ' ok



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Helmut Weber" wrote in message
...
Hi everybody,

is there something wrong with this code
or is something wrong with Excel?

'ActiveSheet.CustomProperties.Add Name:="x", Value:=1 ' ok

MsgBox ActiveSheet.CustomProperties("x").Value ' Type mismatch
MsgBox ActiveSheet.CustomProperties(1).Value ' ok

In Word I can refer to a customdocumentproperty by name.
MsgBox ActiveDocument.CustomDocumentProperties("x").Value ' ok

--
Greetings from Bavaria, Germany
Helmut Weber, MVP WordVBA
"red.sys" & chr(64) & "t-online.de"
Word 2002, Windows 2000 (german versions)

Helmut,
Not sure how you managed to get the .Add statement to work, as there are a
few errors in that code :
- It is the "CustomDocumentProperties" collection.
- The collection is part of the Workbook, not Worksheet object.

See here for some help :
http://www.cpearson.com/excel/docprop.htm

NickHK

"Helmut Weber" ...
Hi everybody,

is there something wrong with this code
or is something wrong with Excel?

'ActiveSheet.CustomProperties.Add Name:="x", Value:=1 ' ok

MsgBox ActiveSheet.CustomProperties("x").Value ' Type mismatch
MsgBox ActiveSheet.CustomProperties(1).Value ' ok

In Word I can refer to a customdocumentproperty by name.
MsgBox ActiveDocument.CustomDocumentProperties("x").Value ' ok

--
Greetings from Bavaria, Germany
Helmut Weber, MVP WordVBA
"red.sys" & chr(64) & "t-online.de"
Word 2002, Windows 2000 (german versions)

He is using CustomProperties Nick, not CustomDocumentProperties, wherein
lies his problem.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"NickHK" wrote in message
...
Helmut,
Not sure how you managed to get the .Add statement to work, as there are a
few errors in that code :
- It is the "CustomDocumentProperties" collection.
- The collection is part of the Workbook, not Worksheet object.

See here for some help :
http://www.cpearson.com/excel/docprop.htm

NickHK

"Helmut Weber"
...
Hi everybody,

is there something wrong with this code
or is something wrong with Excel?

'ActiveSheet.CustomProperties.Add Name:="x", Value:=1 ' ok

MsgBox ActiveSheet.CustomProperties("x").Value ' Type mismatch
MsgBox ActiveSheet.CustomProperties(1).Value ' ok

In Word I can refer to a customdocumentproperty by name.
MsgBox ActiveDocument.CustomDocumentProperties("x").Value ' ok

--
Greetings from Bavaria, Germany
Helmut Weber, MVP WordVBA
"red.sys" & chr(64) & "t-online.de"
Word 2002, Windows 2000 (german versions)

Hi everybody,

what is confusing me is that there is without any doubt
not only a CustomDocumentProperties collection,
but a CustomProperties collection as well.

Sub Test00023()
Dim oSht As Worksheet
Dim oPrt As Property
Dim bFnd As Boolean
Dim lPrt As Long
Set oSht = ActiveSheet
bFnd = False
For lPrt = 1 To oSht.CustomProperties.Count
If oSht.CustomProperties(lPrt).name = "x" Then
bFnd = True
End If
Next
If bFnd = False Then
ActiveSheet.CustomProperties.Add name:="x", Value:=1
End If
On Error Resume Next
MsgBox ActiveSheet.CustomProperties("x").Value ' Type mismatch
If Err.Number 0 Then
MsgBox Err.Number & " = " & Err.Description
End If
MsgBox ActiveSheet.CustomProperties(1).Value ' ok
End Sub

- It is the "CustomDocumentProperties" collection.
- The collection is part of the Workbook, not Worksheet object.

Whereas the CustomProperties collection seems to be
a part of the worksheet object.

I was wondering, whether a CustomProperty
can, seemably, not be addressed by name,
and what a name at all would be good for, then.

Not that I couldn't work around it, no problem at all.

I was just curious about an apparently
faulty design of the CustomProperties collection.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Helmut,
Whilst Help does mention the DocumentProperties collection, I find nothing
on the CustomProperties collection, for XL2K.
You code will not compile on my version, as I now see this is a XL2003
feature designed to work with XML :
http://msdn.microsoft.com/library/en...asp?frame=true

So I guess Item can only return the Property value by Index, not by
key/name.
As I don't have this version of Excel, I can you much.

NickHK

"Helmut Weber" wrote in message
...
Hi everybody,

what is confusing me is that there is without any doubt
not only a CustomDocumentProperties collection,
but a CustomProperties collection as well.

Sub Test00023()
Dim oSht As Worksheet
Dim oPrt As Property
Dim bFnd As Boolean
Dim lPrt As Long
Set oSht = ActiveSheet
bFnd = False
For lPrt = 1 To oSht.CustomProperties.Count
If oSht.CustomProperties(lPrt).name = "x" Then
bFnd = True
End If
Next
If bFnd = False Then
ActiveSheet.CustomProperties.Add name:="x", Value:=1
End If
On Error Resume Next
MsgBox ActiveSheet.CustomProperties("x").Value ' Type mismatch
If Err.Number 0 Then
MsgBox Err.Number & " = " & Err.Description
End If
MsgBox ActiveSheet.CustomProperties(1).Value ' ok
End Sub

- It is the "CustomDocumentProperties" collection.
- The collection is part of the Workbook, not Worksheet object.

Whereas the CustomProperties collection seems to be
a part of the worksheet object.

I was wondering, whether a CustomProperty
can, seemably, not be addressed by name,
and what a name at all would be good for, then.

Not that I couldn't work around it, no problem at all.

I was just curious about an apparently
faulty design of the CustomProperties collection.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

It's in XP too Nick.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"NickHK" wrote in message
...
Helmut,
Whilst Help does mention the DocumentProperties collection, I find nothing
on the CustomProperties collection, for XL2K.
You code will not compile on my version, as I now see this is a XL2003
feature designed to work with XML :

http://msdn.microsoft.com/library/en...asp?frame=true

So I guess Item can only return the Property value by Index, not by
key/name.
As I don't have this version of Excel, I can you much.

NickHK

"Helmut Weber" wrote in message
...
Hi everybody,

what is confusing me is that there is without any doubt
not only a CustomDocumentProperties collection,
but a CustomProperties collection as well.

Sub Test00023()
Dim oSht As Worksheet
Dim oPrt As Property
Dim bFnd As Boolean
Dim lPrt As Long
Set oSht = ActiveSheet
bFnd = False
For lPrt = 1 To oSht.CustomProperties.Count
If oSht.CustomProperties(lPrt).name = "x" Then
bFnd = True
End If
Next
If bFnd = False Then
ActiveSheet.CustomProperties.Add name:="x", Value:=1
End If
On Error Resume Next
MsgBox ActiveSheet.CustomProperties("x").Value ' Type mismatch
If Err.Number 0 Then
MsgBox Err.Number & " = " & Err.Description
End If
MsgBox ActiveSheet.CustomProperties(1).Value ' ok
End Sub

- It is the "CustomDocumentProperties" collection.
- The collection is part of the Workbook, not Worksheet object.

Whereas the CustomProperties collection seems to be
a part of the worksheet object.

I was wondering, whether a CustomProperty
can, seemably, not be addressed by name,
and what a name at all would be good for, then.

Not that I couldn't work around it, no problem at all.

I was just curious about an apparently
faulty design of the CustomProperties collection.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"