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

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

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

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

Hi again

Thanks for you reply.

I understand, and this solves it if I will use Excel-templates.
But our solution is a COM-Addin.

I have tried declaring a function in my Visual Studio COM Addin project
but Excel don't seem to find it.
I guess this is possible if you declare the function correctly and in the
correct module/class.

Do you have any experience in this?

Thanks in advance

--
Peter Karlström
Midrange AB
Sweden


"Gary''s Student" wrote:

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

I have little experience with COM addins.

Can you first try exercising the function in a worksheet and then make the
COM addin?? From Excel:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the UDF in and close the VBE window

Then try the UDF in a cell. BE SURE to modify it if your porperty name is
not "Balance"

--
Gary''s Student - gsnu200753


"Peter Karlström" wrote:

Hi again

Thanks for you reply.

I understand, and this solves it if I will use Excel-templates.
But our solution is a COM-Addin.

I have tried declaring a function in my Visual Studio COM Addin project
but Excel don't seem to find it.
I guess this is possible if you declare the function correctly and in the
correct module/class.

Do you have any experience in this?

Thanks in advance

--
Peter Karlström
Midrange AB
Sweden


"Gary''s Student" wrote:

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