How do I add a customdocumentproperty to store an incremental value?? 1

[runtimeFrankie]

Hi All,

I am trying to add a customdocumentproperty to store a value so my application knows which cell to enter data into a (excel) spreadsheet.
I may not be doing this the best way (I am new to this) but this is how my failed attempt is going so far:-

Dim DP As DocumentProperties
Set DP = ActiveWorkbook.CustomDocumentProperties

DP.Add Name:="CurrentRow", LinkToContent:=False, _
Type:=msoPropertyTypeNumber, Value:=3

VBA doesn't like the above (the property name already holds the name value for my form) and I get a runtime error telling me that "Method 'Add' of object 'DocumentProperties' failed" but I don't know what I am doing wrong and I can't find the answer in my book (Mastering VBA6).

Any advice would be appreciated.

Thanks in advance.

runtimeFrankie

 

[beniez]

Hi Frankie

You can only *add* a custom property once - after that, if you try to add it again, it throws an error, as you've discovered.

Quick fix:

On Error Resume Next
DP.Add Name:="CurrentRow", LinkToContent:=False, _
Type:=msoPropertyTypeNumber, Value:=3
DP.Item("CurrentRow&quot.Value = 4

Otherwise you should check for the existence of the property, and if it exists, set its value.

HTH

Ben

 

[runtimeFrankie]

Thanks Ben,

I cheated and got around this another way but have made a note for my next bash.

Would the above work if I were to assign the item value to the value of an integer + 1.

i.e. DP.Item("CurrentRow&quot.Value = intRowNow + 1 ??

Thanks again.

RTF

 

[beniez]

Frankie

That looks fine to me, though I haven't tested it.

Ben