Right click your sheet tab, view code and paste the code in. Change A1 to
the cell you want
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
On Error Resume Next
Application.EnableEvents = False
ActiveSheet.Name = Target.Value
Application.EnableEvents = True
End If
End Sub
If you are looking at naming all tabs in a workbook use the workbook level
Sheet Change event. Incase you are new to VBA set the security level to
low/medium in (Tools|Macro|Security). From workbook press Alt+F11 to launch
VBE (Visual Basic Editor). From the left treeview search for the workbook
name and click on + to expand it. Within that you should see the following
VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
This Workbook
Double click 'This WorkBook' and paste the below code to the right code pane.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$A$1" And Target.Text <> "" Then
On Error Resume Next
Application.EnableEvents = False
Sh.Name = Target.Text
Application.EnableEvents = True
End If
End Sub
Walrus, the sheet event will change the sheet name as soon as the cell value
is changed. The macro which you posted needs to be run separately to change
the tab names
Nor did yours trap it, it just ignored it. Better to handle it IMO
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Application.EnableEvents = False
ActiveSheet.Name = ValidName(Target.Value)
Application.EnableEvents = True
End If
End Sub
Function ValidName(ByVal TheFileName As String) As String
Dim RegEx As Object
Set RegEx = CreateObject("vbscript.regexp")
RegEx.Global = True
RegEx.Pattern = "[\\/:\*\?""<>\|]"
ValidName = RegEx.Replace(TheFileName, "")
Set RegEx = Nothing
End Function
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.