Hello Peter,
From your post, my understanding on this issue is: you want to know how to
display a custom property value automatically when the property is changed
in a COM add-in for Excel. If I'm off base, please feel free to let me know.
I also notice that you once pasted the same question in newsgroup
microsoft.public.excel.worksheet.functions, and a community member
explained how to do it in VBA macro:
Function balance() As Variant
balance = ActiveWorkbook.CustomDocumentProperties("Balance").Value
End Function
In order to do it in COM addin, I think we could register the document open
event and update the cell's value according to the Customer Document
properties in the event handler. When an Excel workbook is opened, it is
not allowed to modify the custom document properties outside the workbook,
so there are two scenarios where we need to update the cells:
1. When a workbook is opened.
2. When the custom document property is changed inside the workbook (e.g,
in macro or COM add-in code)
For the first scenario, we could add the event handler for Workbook open:
applicationObject.WorkbookOpen += new
Microsoft.Office.Interop.Excel.AppEvents_WorkbookOpenEventHandler(applicatio
nObject_WorkbookOpen);
void applicationObject_WorkbookOpen(Microsoft.Office.Interop.Excel.Workbook
Wb)
{
DocumentProperties properties =
(DocumentProperties)Wb.CustomDocumentProperties;
if (properties["Balance"] != null)
{
Excel.Worksheet sheet1 = (Excel.Worksheet)Wb.Worksheets[1];
Excel.Range range = (Excel.Range)sheet1.Cells[1, 1];
range.Value2 = properties["Balance"].Value;
}
}
In the code above, I suppose you have a custom document property:
"Balance", and I set the cell A1 in worksheet1 as its value.
For the second scenario, we could programmatically update the cell after
the code that change the custom document property. For instance,
DocumentProperties properties =
(DocumentProperties)Wb.CustomDocumentProperties;
properties["Balance"].Value = "123"; // the "Balance" property is changed
Excel.Worksheet sheet1 = (Excel.Worksheet)Wb.Worksheets[1];
Excel.Range range = (Excel.Range)sheet1.Cells[1, 1];
range.Value2 = properties["Balance"].Value // update the cell's value
according to the new property value
Please let me know if you have any other concerns, or need anything else.
Sincerely,
Jialiang Ge (
[email protected], remove 'online.')
Microsoft Online Community Support
==================================================
For MSDN subscribers whose posts are left unanswered, please check this
document:
http://blogs.msdn.com/msdnts/pages/postingAlias.aspx
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications. If you are using Outlook Express/Windows Mail, please make sure
you clear the check box "Tools/Options/Read: Get 300 headers at a time" to
see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
