In this tutorial I demonstrate how to automatically name an Excel worksheet based on a value entered in a cell on that worksheet. To start with you will need to copy the VBA code shown below and then follow the instructions.
If Not Intersect(Target, Range("C2")) Is Nothing Then If Range("C2") = Empty Then ActiveSheet.Name = "Client Unspecified-" & ActiveSheet.Index Else ActiveSheet.Name = Range("C2") End If End If
- Open the Visual Basic Editor (VBE) using the shortcut key ALT F11.
- In the VBE’s Project Explorer, which is the pane on the left (if it’s not visible, CTRL R will open it), select the sheet that you want to add the functionality to.
- In the Code window, which is the window to the right of the Project Explorer (if it’s not visible, F7 will open it), look for the drop-down at the top of the window that currently displays the word General. Change this to Worksheet.
- In the drop-down to the right of the drop-down that now displays Worksheet, select Change as we are going to trigger the VBA code whenever there is a change to the worksheet.
- Between the line that starts Private Sub Worksheet_change and the line that reads End Sub, paste the VBA code. You may need to make changes to the code so it works for you. In the first, second and fifth line of the code that you pasted, change Range(“C2”) to the cell address where you plan to enter the worksheet name. In the third line where it says Client Unspecified- change this to the name you want to give to the worksheet if no value is provided.
- Your code is now ready to go. Close down the VBE with ALt F11 and see if it works!