Adding Custom property

[Joerg]

Hello World!

How can I add a custom property to the active workbook? Following code looks
OK to me, but produces a run-time error (invalid procedure call or
argument):

ActiveWorkbook.CustomDocumentProperties.Add Name:="NumberOfSheets",
_
LinkToContent:=False, _
Type:=msoPropertyTypeNumber, _
Value:=ActiveWorkbook.Sheets.Count

Thanks,

Joerg Mochikun

 

[NickHK]

Joerg,
Your code works for me in XL2002, but I have had difficulties sometimes
working with this collection.
Make sure this property does not already exist
Close the WB, open it again and try the code.

NickHK

 

[Joerg]

Thanks for your quick reply. Property doesn't exist. If created manually,
then using it (e.g.
ActiveWorkbook.CustomDocumentProperties("NumberOfSheets") =
ActiveWorkbook.Sheets.Count)
works fine. Just creating it with VBA is the problem. I'm using XL2003.

Joerg

 

[Guest]

Not much of an answer, but, wanted to let you know that your code worked for
me as well. I have xl2003. The only problem I had was that it errors out
the second time I run it because the property already exists by then.

 

[Joerg]

Thanks. Could you then tell me the value for msoPropertyTypeNumber? When I
debug the code the value for msoPropertyTypeNumber is empty. Could explain
the error, but I found no value table in XL Help and therefore can't try to
change to an absolute value.

Joerg

 

[Joerg]

Just want to let you know that the code works fine when the macro is
included in the workbook where the custom property is to be craeted.
However I try to run it from my Personal.xls, so that I can add the property
to any (active) workbook. This attempt failed so far...

Joerg

 

[Guest]

Joerg,

I added the code as macro to my personal.xls and it worked - it added the
property to the active workbook.

msoPropertyTypeNumber value is 1.

 

[Joerg]

Hmmm... I'm stuck. The code works indeed from Personal.xls when I define
msoPropertyTypeNumber =1, but why do I have to (re)define a built- in
constant?
Thanks anyway for your help.

Joerg

 

[NickHK]

Joerg,
Only thing I think is you do not have reference to the Office xx Object
Library in that WB as it is an Office enum, not Excel.
Check the references.

NickHK

 

[Joerg]

Bingo! Thanks NickHK! My Personal.xls is a very old friend and obviously not
quite up-to-date. But now that I know the cause I keep the declaration of
msoPropertyTypeNumber to make my code backward compatible.

Cheers,

Joerg Mochikun