Setting the "Title" property of an excel spreadsheet

First of all, I'm a total Excel interop noob. I'm trying to get a date from a cell and then set the title of the document before the document gets saved, to be the month of the date. This is my code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ThisWorkbook.Title = DateTime.Month(ThisWorkbook.Sheets("Sheet1").Cell("A10")) End Sub 

I'm not sure that anything is working. I set a breakpoint on the code, but I can't "run" it because it's not a macro, but an event handler, so I don't think the breakpoint is going to work. I don't get any errors. I don't even know that ThisWorkbook.Title is what I want and I'm not even sure about getting the month from the cell.

What do you want the result of the macro to be? Are you trying to change the file name of the saved document?

So you just want the month portion of the date located in cell A10? What format is the date in?, i.e. MM/DD/YY or ?

@jbarker2160 @DyRuss Cell A10 has the date. It's just the cell where the user enters the date in the spreadsheet. When you go to the File menu in Excel, on the right side of the page is a list of Properties . I want the Title property to contain the text of the month from the date in the cell. So if the cell is 8/15/2014 , I want the Title property to be set to August

4 Answers 4

To change the 'Title' built in property in Excel:

ActiveWorkbook.BuiltinDocumentProperties("Title") = "My Title Name"

The title of the document is a "Built In" property - this is the info that appears when you right click on the file and look at the properties.

The name of the spreadsheet is set on save, so you will want to save the file with a new name if you want to see the date on the file itself

A code something like this should give you the result you desire:
(note that this code is VBA, so it may need some tweaking to work in interop.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim FilePath As String Dim varName As String On Error GoTo ErrorHandler ' This disables all Excel events. Application.EnableEvents = False ' disable the default behaviour of the save like so: Cancel = True 'you can leave this blank if you want it to save in the default directory FilePath = "C:\The path\To\The File" varName = Format(ThisWorkbook.Sheets("Sheet1").Cell("A10"),"mmmm") ActiveWorkbook.SaveAs Filename:=FilePath & varName & ".xlsx" ErrorExit: ' This makes sure events get turned back on again no matter what. Application.EnableEvents = True Exit Sub ErrorHandler: MsgBox "No value submitted - File Not Saved" Resume ErrorExit End Sub