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

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" <(E-Mail Removed)> wrote in message
news:esl6ca$17f$(E-Mail Removed)...
> 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
>
>

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


"NickHK" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:esl6ca$17f$(E-Mail Removed)...
> > 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
> >
> >
>
>

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" wrote:

> 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
>
>
> "NickHK" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:esl6ca$17f$(E-Mail Removed)...
> > > 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
> > >
> > >
> >
> >
>
>
>

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

"Vergel Adriano" <(E-Mail Removed)> wrote in message
news:AE7FA2D5-9259-4B72-8691-(E-Mail Removed)...
> 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" wrote:
>
> > 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
> >
> >
> > "NickHK" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > news:esl6ca$17f$(E-Mail Removed)...
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
> >

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

"Joerg" <(E-Mail Removed)> wrote in message
news:esldhd$2er$(E-Mail Removed)...
> 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
>
> "Vergel Adriano" <(E-Mail Removed)> wrote in
message
> news:AE7FA2D5-9259-4B72-8691-(E-Mail Removed)...
> > 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" wrote:
> >
> > > 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
> > >
> > >
> > > "NickHK" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > 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" <(E-Mail Removed)> wrote in message
> > > > news:esl6ca$17f$(E-Mail Removed)...
> > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> > >
>
>

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" wrote:

> 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
>
> "Joerg" <(E-Mail Removed)> wrote in message
> news:esldhd$2er$(E-Mail Removed)...
> > 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
> >
> > "Vergel Adriano" <(E-Mail Removed)> wrote in
> message
> > news:AE7FA2D5-9259-4B72-8691-(E-Mail Removed)...
> > > 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" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > > "NickHK" <(E-Mail Removed)> wrote in message
> > > > news:(E-Mail Removed)...
> > > > > 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" <(E-Mail Removed)> wrote in message
> > > > > news:esl6ca$17f$(E-Mail Removed)...
> > > > > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
>
>
>

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


"Vergel Adriano" <(E-Mail Removed)> wrote in message
news:CFE72E32-141A-4076-9673-(E-Mail Removed)...
> 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" wrote:
>
> > 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
> >
> > "Joerg" <(E-Mail Removed)> wrote in message
> > news:esldhd$2er$(E-Mail Removed)...
> > > 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
> > >
> > > "Vergel Adriano" <(E-Mail Removed)> wrote in
> > message
> > > news:AE7FA2D5-9259-4B72-8691-(E-Mail Removed)...
> > > > 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" wrote:
> > > >
> > > > > 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
> > > > >
> > > > >
> > > > > "NickHK" <(E-Mail Removed)> wrote in message
> > > > > news:(E-Mail Removed)...
> > > > > > 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" <(E-Mail Removed)> wrote in message
> > > > > > news:esl6ca$17f$(E-Mail Removed)...
> > > > > > > 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
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> >
> >
> >

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" <(E-Mail Removed)> wrote in message
news:eslm0p$ov2$(E-Mail Removed)...
> 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
>
>
> "Vergel Adriano" <(E-Mail Removed)> wrote in
message
> news:CFE72E32-141A-4076-9673-(E-Mail Removed)...
> > 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" wrote:
> >
> > > 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
> > >
> > > "Joerg" <(E-Mail Removed)> wrote in message
> > > news:esldhd$2er$(E-Mail Removed)...
> > > > 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
> > > >
> > > > "Vergel Adriano" <(E-Mail Removed)> wrote in
> > > message
> > > > news:AE7FA2D5-9259-4B72-8691-(E-Mail Removed)...
> > > > > 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" wrote:
> > > > >
> > > > > > 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
> > > > > >
> > > > > >
> > > > > > "NickHK" <(E-Mail Removed)> wrote in message
> > > > > > news:(E-Mail Removed)...
> > > > > > > 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" <(E-Mail Removed)> wrote in message
> > > > > > > news:esl6ca$17f$(E-Mail Removed)...
> > > > > > > > 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
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > >
> > >
> > >
>
>

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


"NickHK" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:eslm0p$ov2$(E-Mail Removed)...
> > 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
> >
> >
> > "Vergel Adriano" <(E-Mail Removed)> wrote in
> message
> > news:CFE72E32-141A-4076-9673-(E-Mail Removed)...
> > > 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" wrote:
> > >
> > > > 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
> > > >
> > > > "Joerg" <(E-Mail Removed)> wrote in message
> > > > news:esldhd$2er$(E-Mail Removed)...
> > > > > 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
> > > > >
> > > > > "Vergel Adriano" <(E-Mail Removed)> wrote
in
> > > > message
> > > > > news:AE7FA2D5-9259-4B72-8691-(E-Mail Removed)...
> > > > > > 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" wrote:
> > > > > >
> > > > > > > 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
> > > > > > >
> > > > > > >
> > > > > > > "NickHK" <(E-Mail Removed)> wrote in message
> > > > > > > news:(E-Mail Removed)...
> > > > > > > > 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" <(E-Mail Removed)> wrote in message
> > > > > > > > news:esl6ca$17f$(E-Mail Removed)...
> > > > > > > > > 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
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
>
>