Tag Archives: name

Excel Functions and Formulas Hints and Tips Tips Visual Basic For Applications (VBA)

Excel tip: Display the name of the last user to save a file

A colleague came to me today with a request.  She wanted a cell on her worksheet to display the user name of the last person to save her Excel workbook, along with the time it was saved, and asked me if there was some kind of function in Excel that did this.  I told her unfortunately not, but that I could write a VBA routine that would update a particular cell whenever the file was saved.

This, she said, was fine, but she’d prefer that there was a function or some way of doing it which didn’t necessarily link to a hard-coded cell or worksheet in the file.

At first I thought about creating a UDF to do this, but after a while pondering I decided a more elegant way might be to create a Name in the workbook which would be updated every time the file was saved.

Here’s how to do it:-

First of all, create a name in your workbook called ‘lastupdated’.  In the ‘Refers to:’ section of the dialog, create a reference to a text string such as “Last updated by MRichardson on 15/11/2011”, then click ‘Add’ (Don’t forget the quotes in your text string so that Excel recognises it as such).  The completed dialog should look like this:-

Now, to test this, create a reference to your Name in any cell in the workbook by typing in ‘=lastupdated’.  Press enter and your text should appear in the cell.  Magic!

It’s worth pointing out at this stage that names don’t always have to refer to cell or range references.  They can also be used as in-workbook constants of a kind.  I often use this technique when I want to use often occuring values in formulas and functions (such as VAT, for example).

Right.  We’ve done that.  All we now need to do is update the value of the ‘lastupdated’ name whenever the workbook is saved.

Press Alt-F11 to get to the VBA IDE and double-click on the ‘ThisWorkbook’ module.  We want to update everytime the workbook is saved, so we’re going to add our code to the ‘BeforeSave’ event.

The code itself is really quite straightforward and only takes up one line.  Here it is:-

ThisWorkbook.Names("lastupdated").Value = "Last updated by " & Environ("USERNAME") & " on " & Format(Now, "DD/MM/YYYY")

The code updates the value of our Name with the text, concatenated with the username (which is provided by Environ(“USERNAME”) and the ‘Format’ function to display today’s date in the ‘DD/MM/YYYY’ format.

Your code should look like this in the IDE:-

And that’s it.  Compile the project, close the IDE and save your file.  The cell containing the reference to our name should update with the correct details and will every time somebody saves changes to it.  Whenever you or your users open the file they will always know who last modified it.

The beauty of using a Name is that you can put the reference to it in any cell in the workbook, so you don’t have to deal with hard-coded cells which might be overwritten.  If you need to change the cell the details are displayed in, just add the reference to ‘lastupdated’ to somewhere else in your workbook.  Simple!

Comments on this process, as always, are more than welcome.

Data Web Links

Cool stuff done with data: NameVoyager

In my continuing mission to find cool stuff done with data, whether in graphical form or otherwise I was made aware of this site, in which statistics on baby name usage since the 1880s has been collated so you can see visually how popular names have been over time until now.

It’s really quite addictive and works in real time as you type, narrowing down your search and, in the process of doing so changing the display below the search area to match what you’re typing.  You can also narrow your search further by boys or girls, or both.  Double clicking on a baby name in the graph then opens up another page which gives you even more information about that particular name.  It’s quite fascinating and I imagine quite useful if you’re wanting a really unique name that hasn’t been used for some time.

I did a search on my own name:-

Not all that popular really – at its peak there were 494 Mathew’s per million babies born.

Anyway it’s worth having a look at  – one of those sites you can easily spend an hour looking at without realising.  I love the way that the programming behind this makes the back-end information instantly available and the method of querying this data is so simple and foolproof.  Exactly the way data analysis should be!