Naming tabs using Defined Name

pvr928

Well-known Member
Joined
Oct 21, 2002
Messages
790
Is there any way to name a worksheet tab using a Defined Name?

Ie if the Defined Name is "Project A", then the macro/function will name the tab "Project A"?

Any help greatly appreciated.

Cheers

pvr928
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

ken2step

Well-known Member
Joined
Jan 9, 2003
Messages
632
Try placing this code in your ThisWorkbook module. It should rename your sheets as the value in A1 is changed.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
On Error GoTo Rename_Error
If Target.Address = "$A$1" And Len(Target.Value) <> 0 Then _
Sh.Name = Target.Value
Exit Sub
Rename_Error:
MsgBox "The sheet could not be renamed. Please check " & vbCrLf & _
"that you did not use illegal characters."
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,812
Messages
5,574,458
Members
412,595
Latest member
slim313
Top