Excel Yourself

Intheblack, Mar 2008 by Blackwood, Neale

NEALE BLACKWOOD SHOWS HOW TO AUTOMATE SHEET NAMING

Q: Is It possible to link the sheet name to a cell's contents? I want the sheet name to be whatever I enter into cell Al.

A: There is no built-in Excel function or feature to do this. You have to use an event macro. The macro below will change the sheet (tab) name to whatever is In cell A1. It won't change the sheet name if it isn't a valid name. It won't generate any error messages.

Private Sub Worksheet_Change(ByVal Target As Range)

'this macro renames the sheet with the value in cell A1

Dim c As Range

On Error Resume Next

Set c = lntersect(Target, [A1])

If Not (lsEmpty(c)) Then

Me.Name = [A1].Value

End If

End Sub

To use this macro right click the sheet name and select View Code. Type the above macro code in the white code area on the right-hand side of the Visual Basic screen. You could copy the code via the online version of this article. Close the Visual Basic screen and enter something in cell A1 to test the macro. To vary which cell the name Is linked to, change all references In the code from A1 to the cell required. If you are unfamiliar with macro code it would be a good idea to practise on a blank file.

Warning: This method may break external links to the sheet involved. Any closed Excel files that are linked to a sheet will not update their links when a sheet name changes, resulting in broken links when the closed file is opened.

For back issues go to www.cpaaustralia.com.au/links?excel

Neale Blackwood CPA Is a senior business analyst with Access Analytic Solutions, which provides Excel consulting services

Email: nblackwoodeaccessanalytic.com.au