Put this is a standard module:

Function balance() As Variant
balance = ActiveWorkbook.CustomDocumentProperties("Balance") .Value
End Function

and in the worksheet use it like:

=balance()

I like having the name of the UDF match the name of the property.
--
Gary''s Student - gsnu200753


"Peter Karlström" wrote:

Hi

Thanks for your reply.
This will solve the issue on data from Excel to the other system.

But what about if it's the other way around:

Properties in the spreadsheet is updated from the other system outside
Excel, and when you open it, the values in the cells should update to the
values in
the properies. How do you do that?
This property-change can also be done manually from Windows Explorer, but with
this system they are changed before the user opens the file in Excel.

Do you have a clue?

--
Peter Karlström
Midrange AB
Sweden


"Gary''s Student" wrote:

Let's say we have a custom property called "Balance". We want to update this
property to the value in A1 every time A1 on Sheet1 changes value
automatically.

Insert the following in the worksheet code area for Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
Dim dps As DocumentProperties
Set dps = ThisWorkbook.CustomDocumentProperties
ThisWorkbook.CustomDocumentProperties("Balance") = Target.Value
End Sub

Whenever the cell changes value the property will updated.

--
Gary''s Student - gsnu2007a


"Peter Karlström" wrote:

Hi

In a worksheets property dialogue, there is a possibility to add customized
properties and assign them values.
How can I use these properties in a worksheet?
I know how to add these values by code, but they are not automated to
update the values if they change.

In Word you can add a field which points to the property.
Is there a similar behaviour in Excel, and if so; How do a access it?

The plan is to use these properties to communicate values between
Excel and a document handling system, which we already have done with Word.

Thanks in advance

--
Peter Karlström
Midrange AB
Sweden