Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
link comments section of properties dialog box to footer in Excel
Hi
I use the Comments section in Summary of File Properties quite a lot and would like to link the contents into the file footer. I managed this when using Microsoft Word by inserting the field code {COMMENTS} into the footer then creating a macro to update it on file closure. Sadly I have not been able to do this with Excel. I can reference a cell in the footer but cant seem to link the comments section to a cell - can anyone help? Regards Kenny W XP Pro and Office 2003 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
link comments section of properties dialog box to footer in Excel
Sub Macro1()
s = ActiveWorkbook.BuiltinDocumentProperties.Item(5).V alue ActiveSheet.PageSetup.CenterHeader = s End Sub -- Gary''s Student - gsnu2007k "Forum freak (at work)" wrote: Hi I use the Comments section in Summary of File Properties quite a lot and would like to link the contents into the file footer. I managed this when using Microsoft Word by inserting the field code {COMMENTS} into the footer then creating a macro to update it on file closure. Sadly I have not been able to do this with Excel. I can reference a cell in the footer but cant seem to link the comments section to a cell - can anyone help? Regards Kenny W XP Pro and Office 2003 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
link comments section of properties dialog box to footer in Excel
Thanks Gary that worked a treat.
For future reference is it possible to put the value into a cell? Regards Kenny "Gary''s Student" wrote in message ... Sub Macro1() s = ActiveWorkbook.BuiltinDocumentProperties.Item(5).V alue ActiveSheet.PageSetup.CenterHeader = s End Sub -- Gary''s Student - gsnu2007k "Forum freak (at work)" wrote: Hi I use the Comments section in Summary of File Properties quite a lot and would like to link the contents into the file footer. I managed this when using Microsoft Word by inserting the field code {COMMENTS} into the footer then creating a macro to update it on file closure. Sadly I have not been able to do this with Excel. I can reference a cell in the footer but cant seem to link the comments section to a cell - can anyone help? Regards Kenny W XP Pro and Office 2003 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
link comments section of properties dialog box to footer in Ex
Su
Sub Macro1() s = ActiveWorkbook.BuiltinDocumentProperties.Item(5).V alue Range("A1").Value = s End Sub -- Gary''s Student - gsnu2007k "Forum freak (at work)" wrote: Thanks Gary that worked a treat. For future reference is it possible to put the value into a cell? Regards Kenny "Gary''s Student" wrote in message ... Sub Macro1() s = ActiveWorkbook.BuiltinDocumentProperties.Item(5).V alue ActiveSheet.PageSetup.CenterHeader = s End Sub -- Gary''s Student - gsnu2007k "Forum freak (at work)" wrote: Hi I use the Comments section in Summary of File Properties quite a lot and would like to link the contents into the file footer. I managed this when using Microsoft Word by inserting the field code {COMMENTS} into the footer then creating a macro to update it on file closure. Sadly I have not been able to do this with Excel. I can reference a cell in the footer but cant seem to link the comments section to a cell - can anyone help? Regards Kenny W XP Pro and Office 2003 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
link comments section of properties dialog box to footer in Ex
Many,many thanks,
Worked perfectly! "Gary''s Student" wrote in message ... Su Sub Macro1() s = ActiveWorkbook.BuiltinDocumentProperties.Item(5).V alue Range("A1").Value = s End Sub -- Gary''s Student - gsnu2007k "Forum freak (at work)" wrote: Thanks Gary that worked a treat. For future reference is it possible to put the value into a cell? Regards Kenny "Gary''s Student" wrote in message ... Sub Macro1() s = ActiveWorkbook.BuiltinDocumentProperties.Item(5).V alue ActiveSheet.PageSetup.CenterHeader = s End Sub -- Gary''s Student - gsnu2007k "Forum freak (at work)" wrote: Hi I use the Comments section in Summary of File Properties quite a lot and would like to link the contents into the file footer. I managed this when using Microsoft Word by inserting the field code {COMMENTS} into the footer then creating a macro to update it on file closure. Sadly I have not been able to do this with Excel. I can reference a cell in the footer but cant seem to link the comments section to a cell - can anyone help? Regards Kenny W XP Pro and Office 2003 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
link comments section of properties dialog box to footer in Excel
Add this UDF to your workbook.
Function DocProps(prop As String) Application.Volatile On Error GoTo err_value DocProps = ActiveWorkbook.BuiltinDocumentProperties _ (prop) Exit Function err_value: DocProps = CVErr(xlErrValue) End Function In a cell enter =docprops("Comments") Gord Dibben MS Excel MVP On Fri, 2 Jan 2009 14:27:34 -0000, "Forum freak \(at work\)" wrote: Thanks Gary that worked a treat. For future reference is it possible to put the value into a cell? Regards Kenny "Gary''s Student" wrote in message ... Sub Macro1() s = ActiveWorkbook.BuiltinDocumentProperties.Item(5).V alue ActiveSheet.PageSetup.CenterHeader = s End Sub -- Gary''s Student - gsnu2007k "Forum freak (at work)" wrote: Hi I use the Comments section in Summary of File Properties quite a lot and would like to link the contents into the file footer. I managed this when using Microsoft Word by inserting the field code {COMMENTS} into the footer then creating a macro to update it on file closure. Sadly I have not been able to do this with Excel. I can reference a cell in the footer but cant seem to link the comments section to a cell - can anyone help? Regards Kenny W XP Pro and Office 2003 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
link comments section of properties dialog box to footer in Excel
In addition, run this macro get a complete list on a new sheet.
Sub documentprops() 'list of properties on a new sheet rw = 1 Worksheets.Add For Each p In ActiveWorkbook.BuiltinDocumentProperties Cells(rw, 1).Value = p.Name Cells(rw, 4).Value = "=DocProps(" & "A" & rw & ")" rw = rw + 1 Next End Sub Or this one to get a list of Custom Properties if you have any. Sub customprops() rw = 1 Worksheets.Add For Each p In ActiveWorkbook.CustomDocumentProperties Cells(rw, 1).Value = p.Name Cells(rw, 4).Value = p.Value rw = rw + 1 Next End Sub Gord On Fri, 02 Jan 2009 09:03:26 -0800, Gord Dibben <gorddibbATshawDOTca wrote: Add this UDF to your workbook. Function DocProps(prop As String) Application.Volatile On Error GoTo err_value DocProps = ActiveWorkbook.BuiltinDocumentProperties _ (prop) Exit Function err_value: DocProps = CVErr(xlErrValue) End Function In a cell enter =docprops("Comments") Gord Dibben MS Excel MVP On Fri, 2 Jan 2009 14:27:34 -0000, "Forum freak \(at work\)" wrote: Thanks Gary that worked a treat. For future reference is it possible to put the value into a cell? Regards Kenny "Gary''s Student" wrote in message ... Sub Macro1() s = ActiveWorkbook.BuiltinDocumentProperties.Item(5).V alue ActiveSheet.PageSetup.CenterHeader = s End Sub -- Gary''s Student - gsnu2007k "Forum freak (at work)" wrote: Hi I use the Comments section in Summary of File Properties quite a lot and would like to link the contents into the file footer. I managed this when using Microsoft Word by inserting the field code {COMMENTS} into the footer then creating a macro to update it on file closure. Sadly I have not been able to do this with Excel. I can reference a cell in the footer but cant seem to link the comments section to a cell - can anyone help? Regards Kenny W XP Pro and Office 2003 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing data: OLE DB and data link properties dialog | Excel Programming | |||
Importing Data: OLE DB and data link properties dialog | Excel Discussion (Misc queries) | |||
One footer section in Excel | Excel Discussion (Misc queries) | |||
Excel - should be a defined Header footer section within the sheet | Excel Discussion (Misc queries) | |||
excel -10 sections - footer needs to say "Section # Page # | New Users to Excel |