PDA

View Full Version : Custom Document Property Defaults



Linus_99
02-05-2007, 11:24 PM
I have been using VBA to read/write custom document properties via the CustomDocumentProperties collection.

When you open a workbook & go to File -> Properties -> Custom , Microsoft has thoughtfully provided a default list of ~ 27 suggested custom properties: 'Checked By', 'Client', 'Date Completed', ...... 'Typist'.

Can anyone suggest a way that this default list can be addressed and/or edited in VBA, so that it can be populated it with a different list ??

Thanks .... :doh:

Bob Phillips
02-06-2007, 04:10 AM
Public Sub SetCustomProperties(prop As String, val, valtype As MsoDocProperties)

With ActiveWorkbook
On Error Resume Next
.CustomDocumentProperties(prop).Delete
On Error GoTo 0
.CustomDocumentProperties.Add Name:=prop, _
LinkToContent:=False, _
Value:=val, _
Type:=valtype
End With

End Sub

Public Sub TestCustomProperties()

SetCustomProperties "Client", "Exxon", msoPropertyTypeString
SetCustomProperties "Start Date", Date, msoPropertyTypeDate

End Sub

Linus_99
02-06-2007, 01:58 PM
Thanks xld,

Your code sets a new custom property, but it does not change the default list supplied by Microsoft, which includes: 'Checked By', 'Client', 'Date Completed', ...... 'Typist' etc.

Any clues as to how to change the default list ?

Ken Puls
02-06-2007, 10:42 PM
You'd need to go after the BuiltInDocumentProperties:

Dim l As Long
With ActiveWorkbook
For l = 1 To .BuiltinDocumentProperties.Count
.BuiltinDocumentProperties(l).Value = "Modified"
Next l
End With

You can't delete or rename the properties, though, but you can change their values.

Bob Phillips
02-07-2007, 03:26 AM
Thanks xld,

Your code sets a new custom property, but it does not change the default list supplied by Microsoft, which includes: 'Checked By', 'Client', 'Date Completed', ...... 'Typist' etc.

Any clues as to how to change the default list ?
If by this you mean that you want to remove say the Client property, not its value but the property itself, you are out of luck, they are locked down.

Bob Phillips
02-07-2007, 03:27 AM
You'd need to go after the BuiltInDocumentProperties:

Dim l As Long
With ActiveWorkbook
For l = 1 To .BuiltinDocumentProperties.Count
.BuiltinDocumentProperties(l).Value = "Modified"
Next l
End With
You can't delete or rename the properties, though, but you can change their values.

What's the point of this code Ken? All it does is put Modified in a number of properties, and then fails when it tries to pout the text in a numeric property.

Ken Puls
02-07-2007, 10:33 AM
I was just pointing out how you can change the BuiltIn property values, that's all. The main point of the post was that you couldn't delete/modify the actual names.

Should have been more clear on that, and only chosen one, but there you have it.

Linus_99
02-07-2007, 02:28 PM
If by this you mean that you want to remove say the Client property, not its value but the property itself, you are out of luck, they are locked down.
Yes, the aim was to remove the 'Checked By', 'Client', 'Date Completed', ...... 'Typist' properties all together and replace them with our own list of custom properties called say "Prop1", "Prop2", "Prop3" etc.

I suppose the consensus is that it cannot be done ??

Ken Puls
02-07-2007, 03:14 PM
I suppose the consensus is that it cannot be done ??

Correct.

Bob Phillips
02-07-2007, 05:00 PM
But you can still add your own.

Linus_99
02-07-2007, 05:19 PM
Thanks for the replies.

No problems adding/changing/deleting custom properties and values with VBA as discussed above, but it's just really annoying having the Microsoft suggested list of property names in that listbox.

We are trying to deploy some documents (Excel and Word) across an organisation with a standard set of custom properties defined by our client, & it's distracting for users to be prompted with unwanted property names when they opn the document's property tab.

I've searched high & low for how to do this, but it looks like dead end ....

Bob Phillips
02-08-2007, 04:02 AM
How are you prompted. I open properties dozens of times and I have never been propmpted. Those properties are there, but I ignore them.

Linus_99
02-08-2007, 03:04 PM
My mistake: Not prompted, but our application would be improved if they could be replaced by the names of the set of properties we need to use.

Ken Puls
02-08-2007, 03:09 PM
I don't get it. Why? It's hardly like they come up and announce themselves to you unless you go into the screen.

Seems like an easy workaround would be to create a userform that is only populated with the Custom properties if it's really that irritating...

Bob Phillips
02-08-2007, 03:23 PM
I don't get it. Why? It's hardly like they come up and announce themselves to you unless you go into the screen.

Seems like an easy workaround would be to create a userform that is only populated with the Custom properties if it's really that irritating...

Seems to me that someone has too much time on their hands.